• 欢迎访问搞代码网站,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站!
  • 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏搞代码吧

sqlserver另类非递归的无限级分类(存储_sqlserver

sqlserver 搞代码 3年前 (2018-06-17) 143次浏览 已收录 0个评论

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

第一种方案(递归式):

简单的表结构为:
CategoryID int(4),

http://www.gaodaima.com/?p=37577sqlserver另类非递归的无限级分类(存储_sqlserver

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 [email protected]))
set @i=1
end

–得到depth
declare @depth int
set @depth=0
select @depth=depth from tomi_Category where [email protected]
set @[email protected]+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 [email protected]
if(@@ERROR<>0)
BEGIN
ROLLBACK TRAN
set @Success=0
END
COMMIT TRAN

print @CategoryID

每个分类在tomi_CategoryBind有完整的目录结构。。一个分类在tomi_CategoryBind的记录数等于他在tomi_Category的depth值。

图片:
sqlserver另类非递归的无限级分类(存储
2、编辑修改分类(Category_Edit)

复制代码 代码如下:
CREATE proc [dbo].[Category_Edit]
@CategoryID int,
@CategoryName nvarchar(50),
@BindCategoryID int
as
–更新
BEGIN TRAN
update tomi_Category set [email protected] where [email protected]
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 [email protected] and [email protected] and Depth=(select Depth-1 from tomi_Category where [email protected])))
set @is=1
print @is
–更改了深度
if(@is=0)
BEGIN
–得到上级目录的depth
declare @depth int
set @depth=0
select @depth=depth from tomi_Category where [email protected]
set @[email protected]+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 [email protected] order by c.Depth
set @i=1
set @[email protected]
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 [email protected]
set @[email protected]+@@error
update tomi_Category set [email protected] where [email protected]
set @[email protected]+@@error
–插入
insert into tomi_CategoryBind(CategoryID,BindCategoryID,Depth) values(@sCategoryID,@sCategoryID,@Depth)
set @[email protected]+@@error
insert into tomi_CategoryBind(CategoryID,BindCategoryID,Depth) select @sCategoryID,BindCategoryID,Depth from tomi_CategoryBind where [email protected]
set @[email protected]+@@error
set @[email protected]
set @[email protected]+1
–print @sCategoryID
–print @sBindCategoryID
–print @Depth
–print “–”
END
update @tCategoryIDList set FlagID=1 where [email protected]
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 [email protected] or [email protected])
if(@@ERROR<>0)
BEGIN
ROLLBACK TRAN
return 0
END
delete from tomi_CategoryBind where CategoryID in (select CategoryID from tomi_CategoryBind where [email protected] or [email protected])
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就行。
图片:
sqlserver另类非递归的无限级分类(存储
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 [email protected] order by c.Depth
GO

exec Category_UpTree 63919523 这样就可以得到一个分类的完整子目录集,方便吧,只要一条sql.
图片:
sqlserver另类非递归的无限级分类(存储
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 [email protected] order by c.Depth
GO

exec Category_DownTree 21779652 这样可以得到一个分类完整下级目录。比如得到某个分类和其分类的子分类下的所有产品用这个就好。。方便,一条sql.
图片:
sqlserver另类非递归的无限级分类(存储
以上是初稿,只是随意的测试了几次。。。有错误的,还请大家指出。。

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

欢迎大家阅读《sqlserver另类非递归的无限级分类(存储_sqlserver》,跪求各位点评,若觉得好的话请收藏本文,by 搞代码


喜欢 (0)
[搞代码]
分享 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址