sqlserver另類非遞歸的無窮級分類(存儲進程版)。本站提示廣大學習愛好者：（sqlserver另類非遞歸的無窮級分類(存儲進程版)）文章只能為提供參考，不一定能成為您想要的結果。以下是sqlserver另類非遞歸的無窮級分類(存儲進程版)正文
上面是我統計的幾種計劃：
第一種計劃(遞歸式):
簡略的表構造為：
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
