sqlserver另类非递归的无限级分类(存储过程版)

下面是我统计的几种方案： 第一种方案(递归式): 简单的表结构为： CategoryID int(4), CategoryName nvarchar(50), ParentID int(4), Depth int(4) 这样根据ParentID一级级的运用递归找他的上级目录。 还有可

下面是我统计的几种方案： 第一种方案(递归式): 简单的表结构为： CategoryID int(4), CategoryName nvarchar(50), ParentID int(4), Depth int(4) 这样根据ParentID一级级的运用递归找他的上级目录。 还有可以为了方便添加CategoryLeft,CategoryRight保存他的上级目录或下级目录 第二种方案： 设置一个varchar类型的CategoryPath字段来保存目录的完整路径，将父目录id用符号分隔开来。比如：1,5,8,10 第三种方案： 每级分类递增两位数字的方法 示例： 一级分类：01,02,03,04... 二级分类：0101,0102,0103,0104... 三级分类：010101,010102,010103... 分析一下，其实第三种方案并不能真正意义上做无限级的分类，而第二种方案，虽然比较容易得到各上级及下级的分类信息。但，添加和转移分类的时候操作将很麻烦。 而且，也完全违反了数据库设计范式。 其实我也一直在用第二种方案的。为了查找方便，我有时都在新闻表里加上CategoryID和CategoryPath 而我今天要说的算法其实是第二种方案的改进版，一般做分类都是使用一个表格来保存分类信息。 而我这里，要新建两个表格，一个表格是保存分类信息表，一个保存分类关系表。 表结构如下： 表1：tomi_Category CategoryID int(4), '编号 CategoryName nvarchar(50), '分类名称 Depth int(4), '深度 表2：tomi_CategoryBind CategoryID int(4), BindCategoryID int(4), Depth int(4), 添加，编辑，删除操作有点麻烦。。我是直接用存储过程的。。不知道大家能看得懂不。。哈哈。 1、添加分类(Category_Add) 复制代码 代码如下: CREATE proc [dbo].[Category_Add] @CategoryName nvarchar(50), @BindCategoryID int, @CategoryID int output as declare @Success bit set @Success=1 --生成不重复的CategoryID declare @i bit set @i=0 while @i=0 begin set @CategoryID=LEFT(10000000 + CONVERT(bigint, ABS(CHECKSUM(NEWID()))), 8) if(not exists(select CategoryID from tomi_Category where CategoryID=@CategoryID)) set @i=1 end --得到depth declare @depth int set @depth=0 select @depth=depth from tomi_Category where CategoryID=@BindCategoryID set @depth=@depth+1 --插入 BEGIN TRAN insert into tomi_Category(categoryID,CategoryName,Depth) values(@CategoryID,@CategoryName,@Depth) if(@@ERROR<>0) BEGIN ROLLBACK TRAN set @Success=0 END insert into tomi_CategoryBind(CategoryID,BindCategoryID,Depth) values(@CategoryID,@CategoryID,@Depth) if(@@ERROR<>0) BEGIN ROLLBACK TRAN set @Success=0 END insert into tomi_CategoryBind(CategoryID,BindCategoryID,Depth) select @CategoryID,BindCategoryID,Depth from tomi_CategoryBind where CategoryID=@BindCategoryID if(@@ERROR<>0) BEGIN ROLLBACK TRAN set @Success=0 END COMMIT TRAN print @CategoryID 每个分类在tomi_CategoryBind有完整的目录结构。。一个分类在tomi_CategoryBind的记录数等于他在tomi_Category的depth值。 图片： 2、编辑修改分类(Category_Edit) 复制代码 代码如下: CREATE proc [dbo].[Category_Edit] @CategoryID int, @CategoryName nvarchar(50), @BindCategoryID int as --更新 BEGIN TRAN update tomi_Category set CategoryName=@CategoryName where CategoryID=@CategoryID IF @@ERROR<>0 BEGIN ROLLBACK TRAN return 0 END COMMIT TRAN --检测是否更改了上级目录 declare @is bit set @is=0 if(exists(select CategoryID from tomi_CategoryBind where CategoryID=@CategoryID and BindCategoryID=@BindCategoryID and Depth=(select Depth-1 from tomi_Category where CategoryID=@CategoryID))) set @is=1 print @is --更改了深度 if(@is=0) BEGIN --得到上级目录的depth declare @depth int set @depth=0 select @depth=depth from tomi_Category where CategoryID=@BindCategoryID set @depth=@depth+1 --print @depth --更改子目录 declare @i int declare @sCategoryID int declare @sBindCategoryID int declare @tCategoryIDList Table ( CategoryID int, FlagID tinyint ) insert @tCategoryIDList select c.CategoryID,0 from tomi_Category c left join tomi_CategoryBind b on c.CategoryID=b.CategoryID where b.BindCategoryID=@CategoryID order by c.Depth set @i=1 set @sBindCategoryID=@BindCategoryID declare @errs int set @errs=0 BEGIN TRAN while(@i>=1) BEGIN select @sCategoryID=0 select Top 1 @sCategoryID=CategoryID from @tCategoryIDList where FlagID=0 set @i=@@RowCount --print @sCategoryID if @sCategoryID>0 BEGIN --删除，更新 delete from tomi_CategoryBind where CategoryID=@sCategoryID set @errs=@errs+@@error update tomi_Category set depth=@depth where CategoryID=@sCategoryID set @errs=@errs+@@error --插入 insert into tomi_CategoryBind(CategoryID,BindCategoryID,Depth) values(@sCategoryID,@sCategoryID,@Depth) set @errs=@errs+@@error insert into tomi_CategoryBind(CategoryID,BindCategoryID,Depth) select @sCategoryID,BindCategoryID,Depth from tomi_CategoryBind where CategoryID=@sBindCategoryID set @errs=@errs+@@error set @sBindCategoryID=@sCategoryID set @Depth=@Depth+1 --print @sCategoryID --print @sBindCategoryID --print @Depth --print '--' END update @tCategoryIDList set FlagID=1 where CategoryID=@sCategoryID END if(@errs>0) BEGIN ROLLBACK TRAN return 0 END else COMMIT TRAN END 3、删除分类(Category_Del) 会直接删除子分类 复制代码 代码如下: create proc Category_Del @CategoryID int as BEGIN TRAN delete from tomi_Category where CategoryID in (select CategoryID from tomi_CategoryBind where CategoryID=@CategoryID or BindCategoryID=@CategoryID) if(@@ERROR<>0) BEGIN ROLLBACK TRAN return 0 END delete from tomi_CategoryBind where CategoryID in (select CategoryID from tomi_CategoryBind where CategoryID=@CategoryID or BindCategoryID=@CategoryID) if(@@ERROR<>0) BEGIN ROLLBACK TRAN return 0 END COMMIT TRAN 4、分类列表，显示分类(Category_List) 复制代码 代码如下: CREATE proc Category_List as select c.* from tomi_Category c left join tomi_CategoryBind b on c.CategoryID=b.CategoryID where b.Depth=1 order by b.BindCategoryID,c.Depth GO exec Category_List 可以直接让分类等级查询出来。而且显示全部的话，一次查询即可，只需判断depth就行。 图片： 5、上级子分类列表 (Category_upTree) 复制代码 代码如下: Create Proc Category_UpTree @CategoryID int as select c.* from tomi_Category c left join tomi_CategoryBind b on c.CategoryID=b.BindCategoryID where b.CategoryID=@CategoryID order by c.Depth GO exec Category_UpTree 63919523 这样就可以得到一个分类的完整子目录集，方便吧，只要一条sql. 图片： 6、下级子分类列表(Category_downTree) 复制代码 代码如下: Create Proc Category_DownTree @CategoryID int as select c.* from tomi_Category c left join tomi_CategoryBind b on c.CategoryID=b.CategoryID where b.BindCategoryID=@CategoryID order by c.Depth GO exec Category_DownTree 21779652 这样可以得到一个分类完整下级目录。比如得到某个分类和其分类的子分类下的所有产品用这个就好。。方便，一条sql. 图片： 以上是初稿，只是随意的测试了几次。。。有错误的，还请大家指出。。 呵呵。转载请注明链接，博客园首发，多谢。 作者：TomiWong 时间：2010.07.18