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

【网上购物论坛】-IT泡吧![Www.itpob.Cn]网上购物社区! - Powered by Discuz!
http://www.itpob.cn/

最近在做个CMS系统，要做无限级 分类 。开始在网上找，希望能有现成的拿来就用，结果没搜到满意的，要吗操作复杂，要吗结构复杂，于是想自己倒腾一个，结果还算满意。 1、表结构： 2、查询所有分类（树型） Select * from Category Order By OrderPath 3、查

　　

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

1、表结构：

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

Select * from Category Order By OrderPath

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

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

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

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

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

create   proc Proc_InsertCategory(
 @CategoryName varchar(50),
 @ParentId int,
 @Remark varchar(250)
) as
begin
 declare @KeyId varchar(40)
 declare @OrderId int
 declare @CategoryId int 
 declare @Path varchar(900)
 declare @OrderPath varchar(900)
 select @KeyId=NewId()
 if @ParentId > 0
  Select @OrderId=IsNull(Max(OrderId),0) + 1 From Category Where ParentId=@ParentId
 else
  Select @OrderId=IsNull(Max(OrderId),0) + 1 From Category Where ParentId=CategoryId  
 
 Insert Into Category(ChannelId,KeyId,CategoryName,ParentId,OrderId,Child,Remark)
  Values(@ChannelId,@KeyId,@CategoryName,@ParentId,@OrderId,0,@Remark)
 Select @CategoryId=CategoryId From Category Where KeyId=@KeyId
 if @ParentId > 0
 begin
  select @Path=ParentPath,@OrderPath=OrderPath From Category Where CategoryId=@ParentId
  Update Category Set ParentPath=@Path + ',' + Cast(@CategoryId As Varchar(10)),OrderPath=@OrderPath + ',' + Cast(@OrderId As Varchar(10)) Where CategoryId=@CategoryId  
  Update Category Set Child=Child + 1 Where CategoryId=@ParentId
 end
 else
  Update Category Set ParentId=@CategoryId,Path=Cast(@CategoryId As Varchar(10)),OrderPath=Cast(@OrderId As Varchar(10)) Where CategoryId=@CategoryId
 Select @CategoryId
end

5、顺序调整：

--辅助过程
create   Proc Proc_ResetCategoryOrder(
 @CategoryId int,
 @OrderId int
) as
begin
 Update Category
  Set OrderId=@OrderId,OrderPath=(Select OrderPath From Category Where CategoryId=(Select ParentId From Category Where CategoryId=@CategoryId)) + ',' + cast(@OrderId As varchar(10))
 Where CategoryId=@CategoryId

 Update Category
  Set OrderPath=(Select OrderPath From Category Where CategoryId=@CategoryId) + ',' + cast(OrderId As varchar(10))
 Where ParentPath like (Select ParentPath From Category Where CategoryId=@CategoryId) + ',%'
end

--修改分类序号

create   Proc Proc_ChangeCategoryOrder(
 @CategoryId int,
 @NewOrderId int
) as
begin
 exec Proc_ResetCategoryOrder @CategoryId,@NewOrderId
 declare @OldOrderId int
 declare @ParentId int
 declare @cid int
 declare @oid int
 Select @OldOrderId=OrderId,@ParentId=ParentId From Category Where CategoryId=@CategoryId
 if @OldOrderId=@NewOrderId
  return
 declare @Relation_Category cursor

 if @ParentId = @CategoryId
 begin
  if @OldOrderId>@NewOrderId
   set @Relation_Category = cursor for select CategoryId,OrderId+1 From Category Where CategoryId=ParentId And OrderId<@OldOrderId And OrderId>@NewOrderId
  else
   set @Relation_Category = cursor for select CategoryId,OrderId-1 From Category Where CategoryId=ParentId And OrderId>@OldOrderId And OrderId<@NewOrderId
 end
 else
 begin
  if @OldOrderId>@NewOrderId
   set @Relation_Category = cursor for select CategoryId,OrderId+1 From Category Where CategoryId=@ParentId And OrderId<@OldOrderId And OrderId>@NewOrderId
  else
   set @Relation_Category = cursor for select CategoryId,OrderId-1 From Category Where CategoryId=@ParentId And OrderId>@OldOrderId And OrderId<@NewOrderId
 end
 open @Relation_Category
 fetch next from @Relation_Category into @cid,@oid
 while @@fetch_status=0
 begin
  exec Proc_ResetCategoryOrder @cid,@oid 
  fetch next from @Relation_Category into @cid,@oid
 end
 close @Relation_Category
 DEALLOCATE @Relation_Category
end

 6、删除分类：

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

7、修改：

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

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

欢迎朋友们点评哈～