SQL Server教程：另类非递归的无限级分类(存储过程版)

网络上很多无限级的分类，但无非是两种，一种是递归算法，一种是非递归算法。。

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

第一种方案(递归式):

    简单的表结构为：
        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)

exec Category_List  可以直接让分类等级查询出来。而且显示全部的话，一次查询即可，只需判断depth就行。

图片：

5、上级子分类列表 (Category_upTree)

 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.

图片：

以上是初稿，只是随意的测试了几次。。。有错误的，还请大家指出。。

呵呵。转载请注明链接，博客园首发，多谢。