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

1、表结构：

 

 

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

Code
1Select * from Category Order By OrderPath
 

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

Code
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：添加分类（存储过程实现）： 

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

--辅助过程

Code
 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
 

--修改分类序号

Code
 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、删除分类：

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

7、修改：

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