SQL SERVER另类非递归的无限级分类(存储过程版)
2022-11-12 09:52:21
内容摘要
这篇文章主要为大家详细介绍了SQL SERVER另类非递归的无限级分类(存储过程版),具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!下面是我统
文章正文
这篇文章主要为大家详细介绍了SQL SERVER另类非递归的无限级分类(存储过程版),具有一定的参考价值,可以用来参考一下。
对此感兴趣的朋友,看看idc笔记做的技术笔记!
下面是我统计的几种方案:第一种方案(递归式): 简单的表结构为: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_CategoryCategoryID int(4), '编号CategoryName nvarchar(50), '分类名称Depth int(4), '深度表2:tomi_CategoryBindCategoryID int(4),BindCategoryID int(4),Depth int(4),添加,编辑,删除操作有点麻烦。。我是直接用存储过程的。。不知道大家能看得懂不。。哈哈。1、添加分类(Category_Add)代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | <code> 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 </code> |
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 | <code> 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 </code> |
代码如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | <code> 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 </code> |
代码如下:
1 2 3 4 5 6 7 | <code> 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 </code> |
代码如下:
1 2 3 4 5 6 7 | <code> 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 </code> |
代码如下:
1 2 3 4 5 6 7 | <code> 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 </code> |
注:关于SQL SERVER另类非递归的无限级分类(存储过程版)的内容就先介绍到这里,更多相关文章的可以留意
代码注释