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

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

1、表结构：

 

 

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

1Select * from Category Order By OrderPath 

 

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

1a、直接下属：Select * From Category Where ParentId=10 
2
3   b、本身及所有子节点：Select * From Category Where ParentPath Like '10%' Order By OrderPath 
4
5   c、所有下属子节点（不含本身）：Select * From Category Where ParentPath Like '10,%' Order By OrderPath 
6

 

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

 1create   proc Proc_InsertCategory(
 2 @CategoryName varchar(50),
 3 @ParentId int,
 4 @Remark varchar(250)
 5) as
 6begin
 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 
18 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
30end 
31
32

 

5、顺序调整：

--辅助过程

 1create   Proc Proc_ResetCategoryOrder(
 2 @CategoryId int,
 3 @OrderId int
 4) as
 5begin
 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
10 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) + ',%'
13end 
14

 

--修改分类序号

 1create   Proc Proc_ChangeCategoryOrder(
 2 @CategoryId int,
 3 @NewOrderId int
 4) as
 5begin
 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
16 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
39end 
40

 

6、删除分类：

1 Delete From Category Where ParentPath Like (Select ParentPath From Category Where CategoryId=@CategoryId) + '%' 

 

7、修改：

就只是修改名称和备注，直接更新就可以了

开始的时候觉得好像很难，做出来了才发现，很简单的嘛