[原] 几种常用数据库树形处理

华芸智森 2011-11-06 10:42:32
加精

提示一下:只对初学者,SQL的牛人多,你们就不要看了... 呵呵.

本文只贴出我自己在设计中常用到的,对于那些通用的,递归处理的,已经满天飞.所以在这里不描述了.

1.父/子 描述 + 层级索引

我们一般描述一个有父子结构的组织时(如 BOM,地区,部门,商品分类 等),都习惯性地把它记录为 父/子 的形式,这样,在设计上有很高的弹性,可以无限分层.但在一些特殊的场合,如既要满足弹性的需求,又要满足速度的要求时,可以在原的基础上,加上一个层级的索引和层编号.并设置为唯一索引.

不过,这个有一个唯一的缺点,就是编码复杂.

下面的例子是以部门为例子,节点的层级索引和层编号,节点的移动,修改,等.都能过触发器来自动生成.



IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE [NAME]='BASE_Dept')

	DROP Table BASE_Dept



GO



-----------------------------------

-- 部门资料信息

-----------------------------------

CREATE TABLE BASE_Dept(

	BigParDeptAuID	BigInt			Not NULL,--父系统编号		

	BigDeptAuID		BigInt			Not NULL,--系统编号	

	VarDeptID		VarChar(32)				,--部门编号[人工录入]		



	VarSearch		VarChar(256)	Not NULL,--检索编码用于 LINK 检索，系统生成。格式如： ,12,23,54,

	IntLayerID		Int				Not NULL,--检索层编号. IntLayerID.[ 顶层为 1 ]



	VarDeptName		VarChar(64)		Not NULL,--部门中文名称	名称=所属组织机构名称+部门名称+组别名称

CONSTRAINT PK_BASE_Dept PRIMARY KEY CLUSTERED 

(

	 BigDeptAuID  ASC

))  



GO



CREATE INDEX IX_BASE_Dept_VarSearch ON BASE_Dept(VarSearch,IntLayerID)   

INCLUDE (BigDeptAuID,VarDeptName) 



GO



CREATE INDEX IX_BASE_Dept_BigParDeptAuID ON BASE_Dept(BigParDeptAuID)  



GO



CREATE INDEX IX_BASE_Dept_VarDeptID ON BASE_Dept(VarDeptID) 



GO



IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE [NAME]='Vew_BASE_Dept')

	DROP VIEW Vew_BASE_Dept

	

GO



IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE [NAME]='fun_BASE_Dept_Check_VarDeptID')

	DROP FUNCTION fun_BASE_Dept_Check_VarDeptID



GO



/*************************************************/

--对 VarDeptID 列进行约束检查函数.

--约束规则:表中不能出现两个相同的部门编号.

/*************************************************/

CREATE FUNCTION fun_BASE_Dept_Check_VarDeptID(

									@BigDeptAuID BigInt,

									@VarDeptID VarChar(32))

RETURNS VarChar(32) BEGIN



	IF ISNULL(@VarDeptID,'')='' RETURN 'E5E8ED3D3B2C4645AE51DD6EA8FA0FBE'

	DECLARE @VarValue VarChar(32)

	select @VarValue=VarDeptID from BASE_Dept where VarDeptID=@VarDeptID And BigDeptAuID<>@BigDeptAuID

	

	IF ISNULL(@VarValue,'')='' SET @VarValue='E5E8ED3D3B2C4645AE51DD6EA8FA0FBE' 

	RETURN @VarValue



END



GO



-------------------------------------

-- 约束 VarDeptID 列.

-------------------------------------

ALTER TABLE BASE_Dept

ADD CHECK (ISNULL(VarDeptID,'')<>dbo.fun_BASE_Dept_Check_VarDeptID(BigDeptAuID,VarDeptID))



/*************************************************/



GO



IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE [NAME]='pro_BASE_Dept_CreateSearch')

	DROP Proc pro_BASE_Dept_CreateSearch



GO



-----------------------------------------------------------------

--生成并插入搜索字符. 

-----------------------------------------------------------------

CREATE PROC pro_BASE_Dept_CreateSearch(

		@BigDeptAuID	BIGINT

) AS BEGIN



	DECLARE @VarSearch		VarChar(256)

	DECLARE @IntLayerID		Int

	DECLARE @IntLoop		Int

	DECLARE @BigParDeptAuID	BigInt

	DECLARE @BigNextAuID	BigInt

	

	SET @IntLoop=0

	SET @BigNextAuID=@BigDeptAuID

	SET @VarSearch=',' + LTRIM(@BigNextAuID) + ','

	SET @IntLayerID=1

	

	IF NOT EXISTS(SELECT 1 FROM BASE_Dept WHERE BigDeptAuID=@BigDeptAuID) BEGIN

		RETURN -1

	END

	

	WHILE @IntLoop<999 BEGIN

		

		SET @BigParDeptAuID=0

		SELECT @BigParDeptAuID=BigParDeptAuID FROM BASE_Dept WHERE BigDeptAuID=@BigNextAuID

		IF NOT EXISTS(SELECT 1 FROM BASE_Dept WHERE BigDeptAuID=@BigParDeptAuID) BEGIN

			BREAK;

		END ELSE BEGIN

			SET @BigNextAuID=@BigParDeptAuID

			SET @IntLoop=@IntLoop+1

			SET @IntLayerID=@IntLayerID+1

			SET @VarSearch=',' + LTRIM(@BigNextAuID) + @VarSearch

		END

	END

	

	ENDLOOP:

	IF @IntLayerID=1 BEGIN

		UPDATE BASE_Dept SET VarSearch=@VarSearch,IntLayerID=@IntLayerID,BigParDeptAuID=0 WHERE BigDeptAuID=@BigDeptAuID

	END ELSE BEGIN

		UPDATE BASE_Dept SET VarSearch=@VarSearch,IntLayerID=@IntLayerID WHERE BigDeptAuID=@BigDeptAuID

	END

	

	RETURN 0

	

END



GO



------------------------------------------------------

-- 插入搜索字符.

------------------------------------------------------

CREATE TRIGGER TR_BASE_Dept_CreateSearch ON dbo.BASE_Dept

AFTER INSERT, UPDATE AS BEGIN

	DECLARE @BigAuID BIGINT

	SELECT @BigAuID=MIN(BigDeptAuID) FROM INSERTED

	WHILE NOT @BigAuID IS NULL BEGIN

		EXEC pro_BASE_Dept_CreateSearch @BigAuID

		SELECT @BigAuID=MIN(BigDeptAuID) FROM INSERTED WHERE BigDeptAuID>@BigAuID 

	END

END



GO





-----------------------------------------------------

--部门节点移动.

-----------------------------------------------------

GO



IF EXISTS(SELECT * FROM DBO.SYSOBJECTS WHERE [NAME] = 'pro_BASE_Dept_NodeMove')

	DROP PROC pro_BASE_Dept_NodeMove



GO



Create Proc pro_BASE_Dept_NodeMove(

		@BigDeptAuid Bigint,

		@BigNewParDeptAuID Bigint

) AS BEGIN

	

	IF @BigDeptAuid=@BigNewParDeptAuID RETURN -1

	IF @BigDeptAuid<=0 RETURN -1

	

	DECLARE @BigOleParDeptAuID BIGINT

	DECLARE @IntOleParLayerID Int

	

	DECLARE @IntNewParLayerID Int

	

	DECLARE @IntDltLayer Int

	DECLARE @VarOleSearch VarChar(256)

	DECLARE @VarOPSearch VarChar(256)

	DECLARE @VarNPSearch VarChar(256)



	SELECT @BigOleParDeptAuID=BigParDeptAuID,@VarOleSearch=VarSearch FROM BASE_Dept WHERE BigDeptAuid=@BigDeptAuid

	SET @BigOleParDeptAuID=ISNULL(@BigOleParDeptAuID,0)

	SET @VarOleSearch=ISNULL(@VarOleSearch,'')

	

	SELECT @IntOleParLayerID=IntLayerID,@VarOPSearch=VarSearch FROM BASE_Dept WHERE BigDeptAuid=@BigOleParDeptAuID

	SET @IntOleParLayerID=ISNULL(@IntOleParLayerID,0)

	SET @VarOPSearch=ISNULL(@VarOPSearch,'')

	

	SELECT @IntNewParLayerID=IntLayerID,@VarNPSearch=VarSearch FROM BASE_Dept WHERE BigDeptAuid=@BigNewParDeptAuID

	SET @IntDltLayer=ISNULL(@IntNewParLayerID,0)-ISNULL(@IntOleParLayerID,0)

	

	ALTER TABLE BASE_Dept DISABLE TRIGGER TR_BASE_Dept_CreateSearch

	

	UPDATE BASE_Dept SET BigParDeptAuID=@BigNewParDeptAuID WHERE BigDeptAuid=@BigDeptAuid

	IF @BigOleParDeptAuID=0 BEGIN

		IF @BigNewParDeptAuID<>0 BEGIN

			UPDATE BASE_Dept

			SET VarSearch= @VarNPSearch + SUBSTRING(VarSearch,2,LEN(VarSearch)-1),

				IntLayerID=IntLayerID + @IntDltLayer

			WHERE VarSearch LIKE  @VarOleSearch + '%'

		END

	END ELSE IF @BigOleParDeptAuID>0 BEGIN

		IF @BigNewParDeptAuID=0 BEGIN

			UPDATE BASE_Dept 

			SET VarSearch=REPLACE(VarSearch,@VarOPSearch,','),

				IntLayerID=IntLayerID + @IntDltLayer

			WHERE VarSearch LIKE  @VarOleSearch + '%'

		END ELSE IF @BigNewParDeptAuID<>0 BEGIN

			UPDATE BASE_Dept

			SET VarSearch=REPLACE(VarSearch,@VarOPSearch,@VarNPSearch),

				IntLayerID=IntLayerID + @IntDltLayer

			WHERE VarSearch LIKE  @VarOleSearch + '%'



		END

	END

	ALTER TABLE BASE_Dept ENABLE TRIGGER TR_BASE_Dept_CreateSearch 

	

END
...全文
2337 86 打赏 收藏 转发到动态 举报
写回复
用AI写文章
86 条回复
切换为时间正序
请发表友善的回复…
发表回复
fatdidi 2012-05-12
  • 打赏
  • 举报
 回复
谢谢分享
now111 2012-05-12
  • 打赏
  • 举报
 回复
谢谢分享
index_jsf 2011-11-13
  • 打赏
  • 举报
 回复
支持作者，谢谢分享
皮卡 2011-11-11
  • 打赏
  • 举报
 回复
这个可能参考一下
fancy0109 2011-11-11
  • 打赏
  • 举报
 回复
CREATE INDEX IX_BASE_Dept_VarSearch ON BASE_Dept(VarSearch,IntLayerID)
INCLUDE (BigDeptAuID,VarDeptName)

INCLUDE (BigDeptAuID,VarDeptName) 什么意思，请详细说一下，哈哈。
h3929840 2011-11-11
  • 打赏
  • 举报
 回复
虽然看不懂，但还是支持一下，我是初学者
华芸智森 2011-11-11
  • 打赏
  • 举报
 回复
如果是在普通的WEB中,最快的要么是静态化,要么是上CDN.
华芸智森 2011-11-11
  • 打赏
  • 举报
 回复
[Quote=引用 76 楼 superdullwolf 的回复:]

树型结构,就拿常见的Ajax权限菜单来说,也是可以做复杂度评估的.

增删查改,肯定是牺牲“增删改”的性能来提高查的性能.
然后数据要加载到内存,除非“增删改”,不连接数据库.
做到 NoSQL + SQL 是关键.

在内存中,要讲究算法了.

多叉树,可以定义个Node类,包含{父节点编号,子节点顺序表,权限哈希表}
这样,查找

父关系就很容易O(1)
子关系O(∑……
[/Quote]
老狼,如果要用到内存,直接用内存数据库了.....
1.减少编码量.
2.前台访问时直接用SQL,不用培训.

内存数据库的效率也是非常高的.....
如果自己弄缓存,并发时的锁问题都很麻烦....
q465897859 2011-11-10
  • 打赏
  • 举报
 回复
谢谢分享 收藏
koumingjie 2011-11-10
  • 打赏
  • 举报
 回复
先收藏慢慢体会
UPC_思念 2011-11-10
  • 打赏
  • 举报
 回复
可以采用主键手动生成，比如
001
001001
001002
。。。
这样查询父节点下得所有子节点就很容易了，一条sql就搞定了，so easy!
超级大笨狼 2011-11-10
  • 打赏
  • 举报
 回复
树型结构,就拿常见的Ajax权限菜单来说,也是可以做复杂度评估的.

增删查改,肯定是牺牲“增删改”的性能来提高查的性能.
然后数据要加载到内存,除非“增删改”,不连接数据库.
做到 NoSQL + SQL 是关键.

在内存中,要讲究算法了.

多叉树,可以定义个Node类,包含{父节点编号,子节点顺序表,权限哈希表}
这样,查找

父关系就很容易O(1)
子关系O(∑w)的[∑为深度累加,w为每个节点宽度]
哈希表验证权限,每次都是O(1)可以认为不影响整体复杂度.

类似字典树Trie的做法,很简单.
如果d层深度,平均w为每个节点宽度,总数最多w^d

一万个节点,并发响应速度应该是几十到几百毫秒杀的,
比直接连数据库T-SQL快的多,而且并发效果要好.

就是“增删改”工作量要大一些,内存要大一些,
而且对象缓存要多做几层(集群的memCached单个K-V,本服务器的Cache)
“增删改”，要引起多处缓存和存储的一阵痉挛，都是异步的。

分布式算法系统，要做到人类的极致，就这么玩。
你可以证明自己的程序已经优化到最快了，地球上的人已经阻止不了你了。
woxxxnima 2011-11-10
  • 打赏
  • 举报
 回复
高深，学习一下
machfbc 2011-11-10
  • 打赏
  • 举报
 回复
分析的很到位，谢谢分享。
shanshan187 2011-11-09
  • 打赏
  • 举报
 回复


谢谢楼主了， 好人一身平安
cxlxx21 2011-11-09
  • 打赏
  • 举报
 回复
学习中……谢谢分享！！！
wuxiaoqin927 2011-11-09
  • 打赏
  • 举报
 回复
好复杂啊……我崩溃了
钱币老顽童 2011-11-09
  • 打赏
  • 举报
 回复
jcl007_ 2011-11-08
  • 打赏
  • 举报
 回复
好，不错，学习了
yousi95 2011-11-08
  • 打赏
  • 举报
 回复
很好！！！学习了！
加载更多回复（45）
oracle数据库大数据的处理实战课程
本课程主要是通过对主流数据库oracle11的大数据的处理讲解，包括数据的清洗，数据的整合，数据的导入导出，大数据的建表方式，数据挖掘工程师的工作流程，以及银行项目案例讲解，来使学员成为一名优秀的数据库工程师。
树形展示数据——dhtmlxTree的使用
在Java Web项目开发中，一般用JSP做前台数据展示，后台通过SSH框架来进行高效开发，有流程的控制，业务的处理和对数据库的各种处理，而数据则存放在Oracle等选择的数据库中。现在大家想这样一个问题，用户和什么打交道最多？很简单，用户主要是通过页面和系统打交道呢？那么数据的展示方式很多程度上决定着用户对此系统的评价。这里简单总结一下，除了html能够展示的方式其它的几种比较友好，比较实用的数
【MFC】常用控件：树形控件Tree Control
01、目录 文章目录01、目录02、树形控件简介03、树形控件的通知消息04、 树形控件的相关数据结构05、树形控件的创建06、CTreeCtrl类的主要成员函数07、树形控件的应用实例08、小结 02、树形控件简介 树形控件在Windows系统中是很常见的，例如资源管理器左侧的窗口中就有用来显示目录的树形视图。树形视图中以分层结构显示数据，每层的缩进不同，层次越低缩进越多。树形控件的节点一般都由标签和图标两部分组成，图标用来抽象的描述数据，能够使树形控件的层次关系更加清晰。 树形控件在插入新的树节点时
树形结构的数据如何保存到关系型的数据库
需求： 文档型数据，结构是树形的，如图： 想要读取生成树形结构、添加子节点、查找修改数据的代价最小。 看知乎上牛人的答案: 一般比较普遍的就是四种方法：（具体见 SQL Anti-patterns这本书） Adjacency List：每一条记录存parent_id Path Enumerations：每一条记录存整个tree path经过的node枚举 Nested
数据库中存储层次树状数据
无论你要构建自己的论坛，在你的网站上发布消息还是书写自己的CMS程序，你都会遇到要在数据库中存储层次数据的情况。同时，除非你使用一种像XML的数据库，否则关系数据库中的表都不是层次结构的，他们只是一个平坦的列表。所以你必须找到一种把层次数据库转化的方法。 存储树形结构是一个很常见的问题，他有好几种解决方案。主要有两种方法：邻接列表模型和改进前序遍历树算法 在本文中，我们将探讨这两种保存
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章