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

网络整理 - 08-23

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

第一种方案(递归式):

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

图片：

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

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