MYSQL编程：sqlserver另类非递回的无穷级分类(存储...

解决方案提供商开始推动DBaaS浪潮之前，他们应该深入了解究竟什么是DBaaS。上面是我统计的几种计划：

第一种计划(递回式):

复杂的表布局为：
CategoryIDint(4),
CategoryNamenvarchar(50),
ParentIDint(4),
Depthint(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
CategoryIDint(4),"编号
CategoryNamenvarchar(50),"分类称号
Depthint(4),"深度
表2：tomi_CategoryBind
CategoryIDint(4),
BindCategoryIDint(4),
Depthint(4),

增加，编纂，删除操纵有点贫苦。。我是间接用存储历程的。。不晓得人人能看得懂不。。哈哈。
1、增加分类(Category_Add)
复制代码代码以下:
CREATEproc[dbo].[Category_Add]
@CategoryNamenvarchar(50),
@BindCategoryIDint,
@CategoryIDintoutput
as
declare@Successbit
set@Success=1

--天生不反复的CategoryID
declare@ibit
set@i=0
while@i=0
begin
set@CategoryID=LEFT(10000000+CONVERT(bigint,ABS(CHECKSUM(NEWID()))),8)
if(notexists(selectCategoryIDfromtomi_CategorywhereCategoryID=@CategoryID))
set@i=1
end


--失掉depth
declare@depthint
set@depth=0
select@depth=depthfromtomi_CategorywhereCategoryID=@BindCategoryID
set@depth=@depth+1

--拔出
BEGINTRAN
insertintotomi_Category(categoryID,CategoryName,Depth)values(@CategoryID,@CategoryName,@Depth)
if(@@ERROR0)
BEGIN
ROLLBACKTRAN
set@Success=0
END

insertintotomi_CategoryBind(CategoryID,BindCategoryID,Depth)values(@CategoryID,@CategoryID,@Depth)
if(@@ERROR0)
BEGIN
ROLLBACKTRAN
set@Success=0
END

insertintotomi_CategoryBind(CategoryID,BindCategoryID,Depth)select@CategoryID,BindCategoryID,Depthfromtomi_CategoryBindwhereCategoryID=@BindCategoryID
if(@@ERROR0)
BEGIN
ROLLBACKTRAN
set@Success=0
END
COMMITTRAN

print@CategoryID

每一个分类在tomi_CategoryBind有完全的目次布局。。一个分类在tomi_CategoryBind的纪录数即是他在tomi_Category的depth值。

图片：

登录/注册后可看大图

2、编纂修正分类(Category_Edit)
复制代码代码以下:
CREATEproc[dbo].[Category_Edit]
@CategoryIDint,
@CategoryNamenvarchar(50),
@BindCategoryIDint
as
--更新
BEGINTRAN
updatetomi_CategorysetCategoryName=@CategoryNamewhereCategoryID=@CategoryID
IF@@ERROR0
BEGIN
ROLLBACKTRAN
return0
END
COMMITTRAN
--检测是不是变动了下级目次
declare@isbit
set@is=0
if(exists(selectCategoryIDfromtomi_CategoryBindwhereCategoryID=@CategoryIDandBindCategoryID=@BindCategoryIDandDepth=(selectDepth-1fromtomi_CategorywhereCategoryID=@CategoryID)))
set@is=1
print@is
--变动了深度
if(@is=0)
BEGIN
--失掉下级目次的depth
declare@depthint
set@depth=0
select@depth=depthfromtomi_CategorywhereCategoryID=@BindCategoryID
set@depth=@depth+1
--print@depth
--变动子目次
declare@iint
declare@sCategoryIDint
declare@sBindCategoryIDint
declare@tCategoryIDListTable
(
CategoryIDint,
FlagIDtinyint
)
insert@tCategoryIDListselectc.CategoryID,0fromtomi_Categorycleftjointomi_CategoryBindbonc.CategoryID=b.CategoryIDwhereb.BindCategoryID=@CategoryIDorderbyc.Depth
set@i=1
set@sBindCategoryID=@BindCategoryID
declare@errsint
set@errs=0
BEGINTRAN
while(@i>=1)
BEGIN
select@sCategoryID=0
selectTop1@sCategoryID=CategoryIDfrom@tCategoryIDListwhereFlagID=0
set@i=@@RowCount
--print@sCategoryID
if@sCategoryID>0
BEGIN
--删除，更新
deletefromtomi_CategoryBindwhereCategoryID=@sCategoryID
set@errs=@errs+@@error
updatetomi_Categorysetdepth=@depthwhereCategoryID=@sCategoryID
set@errs=@errs+@@error
--拔出
insertintotomi_CategoryBind(CategoryID,BindCategoryID,Depth)values(@sCategoryID,@sCategoryID,@Depth)
set@errs=@errs+@@error
insertintotomi_CategoryBind(CategoryID,BindCategoryID,Depth)select@sCategoryID,BindCategoryID,Depthfromtomi_CategoryBindwhereCategoryID=@sBindCategoryID
set@errs=@errs+@@error
set@sBindCategoryID=@sCategoryID
set@Depth=@Depth+1
--print@sCategoryID
--print@sBindCategoryID
--print@Depth
--print"--"
END
update@tCategoryIDListsetFlagID=1whereCategoryID=@sCategoryID
END
if(@errs>0)
BEGIN
ROLLBACKTRAN
return0
END
else
COMMITTRAN
END


3、删除分类(Category_Del)会间接删除子分类
复制代码代码以下:
createprocCategory_Del
@CategoryIDint
as
BEGINTRAN
deletefromtomi_CategorywhereCategoryIDin(selectCategoryIDfromtomi_CategoryBindwhereCategoryID=@CategoryIDorBindCategoryID=@CategoryID)
if(@@ERROR0)
BEGIN
ROLLBACKTRAN
return0
END
deletefromtomi_CategoryBindwhereCategoryIDin(selectCategoryIDfromtomi_CategoryBindwhereCategoryID=@CategoryIDorBindCategoryID=@CategoryID)
if(@@ERROR0)
BEGIN
ROLLBACKTRAN
return0
END
COMMITTRAN

4、分类列表，显现分类(Category_List)

复制代码代码以下:
CREATEprocCategory_List
as
selectc.*fromtomi_Categorycleftjointomi_CategoryBindbonc.CategoryID=b.CategoryIDwhereb.Depth=1orderbyb.BindCategoryID,c.Depth

GO

execCategory_List能够间接让分类品级查询出来。并且显现全体的话，一次查询便可，只需判别depth就行。
图片：

登录/注册后可看大图

5、下级子分类列表(Category_upTree)
复制代码代码以下:
CreateProcCategory_UpTree
@CategoryIDint
as
selectc.*fromtomi_Categorycleftjointomi_CategoryBindbonc.CategoryID=b.BindCategoryIDwhereb.CategoryID=@CategoryIDorderbyc.Depth
GO

execCategory_UpTree63919523如许就能够失掉一个分类的完全子目次集，便利吧，只需一条sql.
图片：

登录/注册后可看大图

6、上级子分类列表(Category_downTree)
复制代码代码以下:
CreateProcCategory_DownTree
@CategoryIDint
as
selectc.*fromtomi_Categorycleftjointomi_CategoryBindbonc.CategoryID=b.CategoryIDwhereb.BindCategoryID=@CategoryIDorderbyc.Depth
GO

execCategory_DownTree21779652如许能够失掉一个分类完全上级目次。好比失掉某个分类和其分类的子分类下的一切产物用这个就好。。便利，一条sql.
图片：

登录/注册后可看大图

以上是初稿，只是随便的测试了几回。。。有毛病的，还请人人指出。。

呵呵。转载请说明链接，博客园首发，多谢。
TomiWong
工夫：2010.07.18MySQL数据库归MySQLAB公司所有，但是这个软件是开源的，有一个MySQL学习教程可以免费下载。稍俱常识的新入门者都可以轻松实现在一个常见硬件上安装和配置MySQL。

仓酷云

可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。

两个月啃那本sqlserver2005技术内部-存储引擎，花了几个月啃四本书

where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

原来公司用过MYSQL自己也只是建个表写个SQL

对递归类的树遍历很有帮助。个人感觉这个真是太棒了！阅读清晰，非常有时代感。

每天坚持做不一样的是，认真做笔录，定时复习。一个月你就可以有一定的收获。当然如果你想在sql方面有一定的造诣，你少不了需要看很多很多的书籍了。

发几份SQL课件，以飨阅者

varchar(max)\\\\nvarchar(max)类型的引入大大的提高了编程的效率，可以使用字符串函数对CLOB类型进行操作，这是一个亮点。