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

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

1、表结构：

 

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

Select * from Category Order By OrderPath  

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

1 a、直接下属：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

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

1 create 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、顺序调整：

--辅助过程
1 create 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

--修改分类序号

1 create 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、修改：

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

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