快捷導(dǎo)航

sqlserver另類非遞歸的無限級分類(存儲過程版)

更新時間：2010年07月19日 23:13:35 作者：

網(wǎng)絡(luò)上很多無限級的分類，但無非是兩種，一種是遞歸算法，一種是非遞歸算法。。

下面是我統(tǒng)計的幾種方案：

第一種方案(遞歸式):

簡單的表結(jié)構(gòu)為：
CategoryID int(4),
CategoryName nvarchar(50),
ParentID int(4),
Depth int(4)
這樣根據(jù)ParentID一級級的運用遞歸找他的上級目錄。
還有可以為了方便添加CategoryLeft,CategoryRight保存他的上級目錄或下級目錄

第二種方案：
設(shè)置一個varchar類型的CategoryPath字段來保存目錄的完整路徑，將父目錄id用符號分隔開來。比如：1,5,8,10

第三種方案：
每級分類遞增兩位數(shù)字的方法
示例：
一級分類：01,02,03,04...
二級分類：0101,0102,0103,0104...
三級分類：010101,010102,010103...

分析一下，其實第三種方案并不能真正意義上做無限級的分類，而第二種方案，雖然比較容易得到各上級及下級的分類信息。但，添加和轉(zhuǎn)移分類的時候操作將很麻煩。
而且，也完全違反了數(shù)據(jù)庫設(shè)計范式。

其實我也一直在用第二種方案的。為了查找方便，我有時都在新聞表里加上CategoryID和CategoryPath

而我今天要說的算法其實是第二種方案的改進版，一般做分類都是使用一個表格來保存分類信息。
而我這里，要新建兩個表格，一個表格是保存分類信息表，一個保存分類關(guān)系表。

表結(jié)構(gòu)如下：
表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)

復(fù)制代碼代碼如下:

 
CREATE proc [dbo].[Category_Add] 
@CategoryName nvarchar(50), 
@BindCategoryID int, 
@CategoryID int output 
as 
declare @Success bit 
set @Success=1 

--生成不重復(fù)的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有完整的目錄結(jié)構(gòu)。。一個分類在tomi_CategoryBind的記錄數(shù)等于他在tomi_Category的depth值。

圖片：

2、編輯修改分類(Category_Edit)

復(fù)制代碼代碼如下:

 
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) 會直接刪除子分類

復(fù)制代碼代碼如下:

 
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)

復(fù)制代碼代碼如下:

 
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)

復(fù)制代碼代碼如下:

 
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)

復(fù)制代碼代碼如下:

 
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 這樣可以得到一個分類完整下級目錄。比如得到某個分類和其分類的子分類下的所有產(chǎn)品用這個就好。。方便，一條sql.
圖片：