下面是我统计的几种方案：
第一种方案(递归式):
简单的表结构为：
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(@@error0)
begin
rollback tran
set @success=0
end
insert into tomi_categorybind(categoryid,bindcategoryid,depth) values(@categoryid,@categoryid,@depth)
if(@@error0)
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(@@error0)
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 @@error0
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(@@error0)
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(@@error0)
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