在RDBMS中操作 图 树 层次结构 等特殊的数据结构时,我们通常采用2个主要方法:
1.基于迭代/递归
2.具体化描述数据结构的附加信息。
一般模型有:员工组织图(树,层次结构);料表--BOM(有向图);道路系统(无向循环图)
1.迭代/递归
迭代可以迭代图的一个节点,也可以迭代一个层次.后者比前者要快很多.
实现方法:SQL2000通过UDF(用户自定义函数),SQL2005使用CTE。
a.下属问题(通俗说,求子节点)
--这里我使用书上的员工表(表内容如下)
--SQL2000 udf方法:
--SQL2005 CTE
--查询结果
---------------如果需要限制递归的层数------------
--SQL2000
--查询结果
SELECT empid, lvl
FROM dbo.fn_subordinates2(2, NULL) AS S;
PS:这里控制返回的层数 你当然可以用最开始的方法,然后再筛选语句里控制层数
如SELECT empid, lvl FROM dbo.fn_subordinates1(3) AS S where lvl<3; 但是控制本身的递归只能在UDF里面了
--sql2005方法类似
--还有一种偏方:但是不推荐 虽然结果正确 但是会报错
--查询结果
b.祖先(通俗说:求父节点)
其实思路跟子节点差不多
--SQL2000
--查询结果
SELECT empid, lvl
FROM dbo.fn_managers(8, 2) AS M;
--sql2005
C.层次显示
--SQL2000,思路跟下属问题一模一样 只是多了个PATH
--这里的显示分2种
--显示1
select empid ,pos=REPLICATE('-',lvl)+rtrim(empid)
FROM dbo.fn_subordinates3(1, NULL) AS S
ORDER BY PATH
--还有一种
SELECT empid, path
FROM dbo.fn_subordinates3(1, NULL) AS S
ORDER BY PATH
--当然上面的显示方式还很多,自己可以控制 比如不加ORDER 就可以排出不一样的
--SQL2005
;
--结果查询
D.检测循环中异常
说白了 就是检查表里有没出现1-2-4-1这种头接尾的圈.这在现实中是不可能的.一个经理部可能是它手下的手下.-- ||
我们对表做手脚,把老大的经理改成是它的一个员工
--查询结果
--这样管理员可以轻易找到那个错误的地方.
改过来:UPDATE dbo.Employees SET mgrid = NULL WHERE empid = 1;
2.具体化路径
这里就是新增加2列,一列是级别 一列是节点路径,这样可以避免每次都去计算.
2个优点:不需要递归,只需要基于集合 ;查询可以使用到路径索引
a.维护数据
1.添加不管理员工的员工
;
----检测
SELECT empid, mgrid, empname, salary, lvl, path
FROM dbo.Employees
ORDER BY path;
2.移动子树
比如说某个部门来了个新老大,原来部门老大和手下的人都要跟着他.这个时候表里的路径和级别都要更新
Select Empid, Replicate(' | ', Lvl) + Empname As Empname, Lvl, Path
From Dbo.Employees
Order By Path;
--这个是移动之前的层次分布
==接下来我们移动
--移动后结果
--这个是移动之后 大家注意观察7 和 10 两位同志
3.移除子树
就是把某个部门从公司取消掉
--首先查看公司部门当前分布
--查询结果
--接着我们来开始一个部门 比如移除Aaron手下的人
--查询结果
4.查询
这里的查询可就轻松多啦 ~因为路径都有啦~
--查询EMPID为3的手下一批人
--查询结果
b.嵌套集合
是书的作者认为用于树建模最完美最高明的解决方案.
惭愧..太难..准备翻第二次书的时候再研究...