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

时间:2022-03-17 01:50:24

下面是我统计的几种方案: 

第一种方案(递归式): 

简单的表结构为: 
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值。 

图片: 
sqlserver另类非递归的无限级分类(存储过程版)
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就行。 
图片: 
sqlserver另类非递归的无限级分类(存储过程版)
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. 
图片: 
sqlserver另类非递归的无限级分类(存储过程版)
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. 
图片: 
sqlserver另类非递归的无限级分类(存储过程版)
以上是初稿,只是随意的测试了几次。。。有错误的,还请大家指出。。 

呵呵。转载请注明链接,博客园首发,多谢。 
作者:TomiWong 
时间:2010.07.18