无限级分类设计--纯数据库实现

最新推荐文章于 2026-06-24 20:10:14 发布

转载最新推荐文章于 2026-06-24 20:10:14 发布 · 77 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

原文链接：http://www.cnblogs.com/sandou/archive/2009/02/09/1386583.html

标签

#数据库

本文介绍了一种基于SQL Server的无限级分类系统的设计与实现，包括表结构设计、查询、添加、顺序调整、删除和修改等操作的具体实现方法。

最近在做个CMS系统，要做无限级分类。开始在网上找，希望能有现成的拿来就用，结果没搜到满意的，要吗操作复杂，要吗结构复杂，于是想自己倒腾一个，结果还算满意。

1、表结构：

2、查询所有分类（树型）

Code
1

Select * from Category Order By OrderPath

3、查询某个分类（这里假设该节点ID为 10）下属分类

Code
1

a、直接下属：Select * From Category Where ParentId=10
2

b、本身及所有子节点：Select * From Category Where ParentPath Like '10%' Order By OrderPath
4

c、所有下属子节点（不含本身）：Select * From Category Where ParentPath Like '10,%' Order By OrderPath
6

4：添加分类（存储过程实现）：

Code
1

create proc Proc_InsertCategory(
2

@CategoryName varchar(50),
3

@ParentId int,
4

@Remark varchar(250)
5

) as
6

begin
7

declare @KeyId varchar(40)
8

declare @OrderId int
9

declare @CategoryId int
10

declare @Path varchar(900)
11

declare @OrderPath varchar(900)
12

select @KeyId=NewId()
13

if @ParentId > 0
14

Select @OrderId=IsNull(Max(OrderId),0) + 1 From Category Where ParentId=@ParentId
15

else
16

Select @OrderId=IsNull(Max(OrderId),0) + 1 From Category Where ParentId=CategoryId
17

Insert Into Category(ChannelId,KeyId,CategoryName,ParentId,OrderId,Child,Remark)
19

Values(@ChannelId,@KeyId,@CategoryName,@ParentId,@OrderId,0,@Remark)
20

Select @CategoryId=CategoryId From Category Where KeyId=@KeyId
21

if @ParentId > 0
22

begin
23

select @Path=ParentPath,@OrderPath=OrderPath From Category Where CategoryId=@ParentId
24

Update Category Set ParentPath=@Path + ',' + Cast(@CategoryId As Varchar(10)),OrderPath=@OrderPath + ',' + Cast(@OrderId As Varchar(10)) Where CategoryId=@CategoryId
25

Update Category Set Child=Child + 1 Where CategoryId=@ParentId
26

end
27

else
28

Update Category Set ParentId=@CategoryId,Path=Cast(@CategoryId As Varchar(10)),OrderPath=Cast(@OrderId As Varchar(10)) Where CategoryId=@CategoryId
29

Select @CategoryId
30

end
31

5、顺序调整：

--辅助过程

Code
1

create Proc Proc_ResetCategoryOrder(
2

@CategoryId int,
3

@OrderId int
4

) as
5

begin
6

Update Category
7

Set OrderId=@OrderId,OrderPath=(Select OrderPath From Category Where CategoryId=(Select ParentId From Category Where CategoryId=@CategoryId)) + ',' + cast(@OrderId As varchar(10))
8

Where CategoryId=@CategoryId
9

Update Category
11

Set OrderPath=(Select OrderPath From Category Where CategoryId=@CategoryId) + ',' + cast(OrderId As varchar(10))
12

Where ParentPath like (Select ParentPath From Category Where CategoryId=@CategoryId) + ',%'
13

end
14

--修改分类序号

Code
1

create Proc Proc_ChangeCategoryOrder(
2

@CategoryId int,
3

@NewOrderId int
4

) as
5

begin
6

exec Proc_ResetCategoryOrder @CategoryId,@NewOrderId
7

declare @OldOrderId int
8

declare @ParentId int
9

declare @cid int
10

declare @oid int
11

Select @OldOrderId=OrderId,@ParentId=ParentId From Category Where CategoryId=@CategoryId
12

if @OldOrderId=@NewOrderId
13

return
14

declare @Relation_Category cursor
15

if @ParentId = @CategoryId
17

begin
18

if @OldOrderId>@NewOrderId
19

set @Relation_Category = cursor for select CategoryId,OrderId+1 From Category Where CategoryId=ParentId And OrderId<@OldOrderId And OrderId>@NewOrderId
20

else
21

set @Relation_Category = cursor for select CategoryId,OrderId-1 From Category Where CategoryId=ParentId And OrderId>@OldOrderId And OrderId<@NewOrderId
22

end
23

else
24

begin
25

if @OldOrderId>@NewOrderId
26

set @Relation_Category = cursor for select CategoryId,OrderId+1 From Category Where CategoryId=@ParentId And OrderId<@OldOrderId And OrderId>@NewOrderId
27

else
28

set @Relation_Category = cursor for select CategoryId,OrderId-1 From Category Where CategoryId=@ParentId And OrderId>@OldOrderId And OrderId<@NewOrderId
29

end
30

open @Relation_Category
31

fetch next from @Relation_Category into @cid,@oid
32

while @@fetch_status=0
33

begin
34

exec Proc_ResetCategoryOrder @cid,@oid
35

fetch next from @Relation_Category into @cid,@oid
36

end
37

close @Relation_Category
38

DEALLOCATE @Relation_Category
39

end
40

6、删除分类：