sqlserver另類非遞歸的無(wú)限級(jí)分類(存儲(chǔ)過(guò)程版)
發(fā)布日期：2022-01-28 17:42 | 文章來(lái)源：源碼之家
第一種方案(遞歸式):
簡(jiǎn)單的表結(jié)構(gòu)為：
CategoryID int(4),
CategoryName nvarchar(50),
ParentID int(4),
Depth int(4)
這樣根據(jù)ParentID一級(jí)級(jí)的運(yùn)用遞歸找他的上級(jí)目錄。
還有可以為了方便添加CategoryLeft,CategoryRight保存他的上級(jí)目錄或下級(jí)目錄 第二種方案：
設(shè)置一個(gè)varchar類型的CategoryPath字段來(lái)保存目錄的完整路徑，將父目錄id用符號(hào)分隔開(kāi)來(lái)。比如：1,5,8,10 第三種方案：
每級(jí)分類遞增兩位數(shù)字的方法
示例：
一級(jí)分類：01,02,03,04...
二級(jí)分類：0101,0102,0103,0104...
三級(jí)分類：010101,010102,010103... 分析一下，其實(shí)第三種方案并不能真正意義上做無(wú)限級(jí)的分類，而第二種方案，雖然比較容易得到各上級(jí)及下級(jí)的分類信息。但，添加和轉(zhuǎn)移分類的時(shí)候操作將很麻煩。
而且，也完全違反了數(shù)據(jù)庫(kù)設(shè)計(jì)范式。 其實(shí)我也一直在用第二種方案的。為了查找方便，我有時(shí)都在新聞表里加上CategoryID和CategoryPath 而我今天要說(shuō)的算法其實(shí)是第二種方案的改進(jìn)版，一般做分類都是使用一個(gè)表格來(lái)保存分類信息。
而我這里，要新建兩個(gè)表格，一個(gè)表格是保存分類信息表，一個(gè)保存分類關(guān)系表。 表結(jié)構(gòu)如下：
表1：tomi_Category
CategoryID int(4), '編號(hào)
CategoryName nvarchar(50), '分類名稱
Depth int(4), '深度
表2：tomi_CategoryBind
CategoryID int(4),
BindCategoryID int(4),
Depth int(4), 添加，編輯，刪除操作有點(diǎn)麻煩。。我是直接用存儲(chǔ)過(guò)程的。。不知道大家能看得懂不。。哈哈。
1、添加分類(Category_Add)
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
每個(gè)分類在tomi_CategoryBind有完整的目錄結(jié)構(gòu)。。一個(gè)分類在tomi_CategoryBind的記錄數(shù)等于他在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
--檢測(cè)是否更改了上級(jí)目錄
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
--得到上級(jí)目錄的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) 會(huì)直接刪除子分類
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 可以直接讓分類等級(jí)查詢出來(lái)。而且顯示全部的話，一次查詢即可，只需判斷depth就行。
圖片：
5、上級(jí)子分類列表 (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 這樣就可以得到一個(gè)分類的完整子目錄集，方便吧，只要一條sql.
圖片：
6、下級(jí)子分類列表(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 這樣可以得到一個(gè)分類完整下級(jí)目錄。比如得到某個(gè)分類和其分類的子分類下的所有產(chǎn)品用這個(gè)就好。。方便，一條sql.
圖片：
以上是初稿，只是隨意的測(cè)試了幾次。。。有錯(cuò)誤的，還請(qǐng)大家指出。。 呵呵。轉(zhuǎn)載請(qǐng)注明鏈接，博客園首發(fā)，多謝。
作者：TomiWong
時(shí)間：2010.07.18
簡(jiǎn)單的表結(jié)構(gòu)為：
CategoryID int(4),
CategoryName nvarchar(50),
ParentID int(4),
Depth int(4)
這樣根據(jù)ParentID一級(jí)級(jí)的運(yùn)用遞歸找他的上級(jí)目錄。
還有可以為了方便添加CategoryLeft,CategoryRight保存他的上級(jí)目錄或下級(jí)目錄 第二種方案：
設(shè)置一個(gè)varchar類型的CategoryPath字段來(lái)保存目錄的完整路徑，將父目錄id用符號(hào)分隔開(kāi)來(lái)。比如：1,5,8,10 第三種方案：
每級(jí)分類遞增兩位數(shù)字的方法
示例：
一級(jí)分類：01,02,03,04...
二級(jí)分類：0101,0102,0103,0104...
三級(jí)分類：010101,010102,010103... 分析一下，其實(shí)第三種方案并不能真正意義上做無(wú)限級(jí)的分類，而第二種方案，雖然比較容易得到各上級(jí)及下級(jí)的分類信息。但，添加和轉(zhuǎn)移分類的時(shí)候操作將很麻煩。
而且，也完全違反了數(shù)據(jù)庫(kù)設(shè)計(jì)范式。 其實(shí)我也一直在用第二種方案的。為了查找方便，我有時(shí)都在新聞表里加上CategoryID和CategoryPath 而我今天要說(shuō)的算法其實(shí)是第二種方案的改進(jìn)版，一般做分類都是使用一個(gè)表格來(lái)保存分類信息。
而我這里，要新建兩個(gè)表格，一個(gè)表格是保存分類信息表，一個(gè)保存分類關(guān)系表。 表結(jié)構(gòu)如下：
表1：tomi_Category
CategoryID int(4), '編號(hào)
CategoryName nvarchar(50), '分類名稱
Depth int(4), '深度
表2：tomi_CategoryBind
CategoryID int(4),
BindCategoryID int(4),
Depth int(4), 添加，編輯，刪除操作有點(diǎn)麻煩。。我是直接用存儲(chǔ)過(guò)程的。。不知道大家能看得懂不。。哈哈。
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
每個(gè)分類在tomi_CategoryBind有完整的目錄結(jié)構(gòu)。。一個(gè)分類在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
--檢測(cè)是否更改了上級(jí)目錄
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
--得到上級(jí)目錄的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
復(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 可以直接讓分類等級(jí)查詢出來(lái)。而且顯示全部的話，一次查詢即可，只需判斷depth就行。
圖片：
5、上級(jí)子分類列表 (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 這樣就可以得到一個(gè)分類的完整子目錄集，方便吧，只要一條sql.
圖片：
6、下級(jí)子分類列表(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 這樣可以得到一個(gè)分類完整下級(jí)目錄。比如得到某個(gè)分類和其分類的子分類下的所有產(chǎn)品用這個(gè)就好。。方便，一條sql.
圖片：
以上是初稿，只是隨意的測(cè)試了幾次。。。有錯(cuò)誤的，還請(qǐng)大家指出。。 呵呵。轉(zhuǎn)載請(qǐng)注明鏈接，博客園首發(fā)，多謝。
作者：TomiWong
時(shí)間：2010.07.18
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信