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)
<div class="codetitle">
<a style="CURSOR: pointer" data="72622" class="copybut" id="copybut72622" onclick="doCopy('code72622')"> 代码如下:
<div class="codebody" id="code72622">
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,@CategoryID,Depth) select @CategoryID,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)
<div class="codetitle">
<a style="CURSOR: pointer" data="64336" class="copybut" id="copybut64336" onclick="doCopy('code64336')"> 代码如下:
<div class="codebody" id="code64336">
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,Depth) values(@sCategoryID,@sCategoryID,@Depth)
set @errs=@errs+@@error
insert into tomi_CategoryBind(CategoryID,Depth) select @sCategoryID,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
