SQL 展现祖孙关系,员工 CLARK 是 KING 的下属,你已经在前一个实例里学到了如何展现这一关系。那么,如果员工 CLARK 同时也是另一个员工的管理者的话,又该如何呢?考虑如下所示的查询。
SQL 展现祖孙关系 问题描述
员工 CLARK 是 KING 的下属,你已经在前一个实例里学到了如何展现这一关系。那么,如果员工 CLARK 同时也是另一个员工的管理者的话,又该如何呢?考虑如下所示的查询。
select ename,empno,mgr
from emp
where ename in ('KING','CLARK','MILLER')
ENAME EMPNO MGR
---------- ---------- ----------
CLARK 7782 7839
KING 7839
MILLER 7934 7782
如上所示,员工 MILLER 是 CLARK 的下属,而 CLARK 又是 KING 的下属。你希望展现从 MILLER 到 KING 整个层次关系。你希望返回如下所示的结果集。
LEAF___BRANCH_ _ _ROOT
----------------------
MILLER-->CLARK-->KING
然而,如果还像前一个实例那样只使用一个自连接查询的话,其实无法显示从上到下整个层次关系。当然你也可以使用两个自连接查询,但你真正需要的可能是能够遍历整个层次关系的通用做法。
SQL 展现祖孙关系 解决方案
本实例不同于前一个实例,正如标题所示,现在我们面对的是一个 3 层的层次关系。有些数据库没有提供能够遍历树形结构数据的函数,我们可以采用 PostgreSQL 和 MySQL 解决方案的做法,但必须再增加一个自连接查询。DB2、SQL Server 和 Oracle 为展现层次关系提供了专门的函数。因此,尽管自连接方案仍然适用于这些数据库,但已经没有必要那么做了。
DB2 和 SQL Server
使用 WITH
递归查询找到 MILLER 的管理者 CLARK,以及 CLARK 的管理者 KING。下面的解决方案用到了 SQL Server 的字符串连接操作符 +
。
1 with x (tree,mgr,depth)
2 as (
3 select cast(ename as varchar(100)),
4 mgr, 0
5 from emp
6 where ename = 'MILLER'
7 union all
8 select cast(x.tree+'-->'+e.ename as varchar(100)),
9 e.mgr, x.depth+1
10 from emp e, x
11 where x.mgr = e.empno
12 )
13 select tree leaf___branch___root
14 from x
15 where depth = 2
对于 DB2 数据库,上述解决方案唯一需要修改的是改用 DB2 的字符串连接操作符||
。除此以外,该解决方案完全适用于 DB2 和 SQL Server。
Oracle
使用 SYS_CONNECT_BY_PATH
函数返回 MILLER、MILLER 的管理者 CLARK 和 CLARK 的管理者 KING。使用 CONNECT BY
子句遍历树形结构。
1 select ltrim(
2 sys_connect_by_path(ename,'-->'),
3 '-->') leaf___branch___root
4 from emp
5 where level = 3
6 start with ename = 'MILLER
7 connect by prior mgr = empno
PostgreSQL 和 MySQL
基于 EMP
表自连接两次返回 MILLER、MILLER 的管理者 CLARK 以及 CLARK 的管理者 KING。下面的解决方案使用了 PostgreSQL 的双竖线||
字符串连接操作符。
1 select a.ename||'-->'||b.ename
2 ||'-->'||c.ename as leaf___branch___root
3 from emp a, emp b, emp c
4 where a.ename = 'MILLER'
5 and a.mgr = b.empno
6 and b.mgr = c.empno
对于 MySQL 用户,要使用 CONCAT
函数,这样上述解决方案就可以像 PostgreSQL 一样正常运行了。
SQL 展现祖孙关系 扩展知识
DB2 和 SQL Server
本解决方案的做法是从叶子节点开始遍历到根节点(你不妨尝试从相反的方向遍历各个节点,这是一次很好的练习)。UNION ALL
的前半部分仅仅找出了员工 MILLER(叶子节点)所在的行。UNION ALL
的后半部分找到了 MILLER 的管理者,然后再找到那个人的管理者,这种找出“管理者的管理者”的处理会一直重复,直至找到最高级别的管理者(根节点)才会停下。DEPTH
值从 0 开始,每找到一层管理者就自动加 1。当执行递归查询时,DB2 会为我们维护一个 DEPTH
值。
如果对
WITH
递归查询感兴趣的话,请参考 Jonathan Gennick 写的一篇有趣且有深度的介绍性文章“Understanding the WITH Clause”,网址是 http://gennick.com/database/understanding-the-with-clause。
接下来,UNION ALL
后半部分的查询把递归视图 X
连接到 EMP
表,从而定义父子关系。下面的查询语句使用了 SQL Server 的字符串连接操作符。
with x (tree,mgr,depth)
as (
select cast(ename as varchar(100)),
mgr, 0
from emp
where ename = 'MILLER'
union all
select cast(x.tree+'-->'+e.ename as varchar(100)),
e.mgr, x.depth+1
from emp e, x
where x.mgr = e.empno
)
select tree leaf___branch___root
from x
TREE DEPTH
---------- ----------
MILLER 0
CLARK 1
KING 2
现在,最重要的问题已经解决了。从 MILLER 开始,我们找出了从下到上的整个层次关系。下面只需要格式化即可。因为树形结构的遍历是递归的,只要把 EMP
表的当前 ENAME
连接到它前面的一个名字即可,如下所示。
select cast(ename as varchar(100)),
mgr, 0
from emp
where ename = 'MILLER'
union all
select cast(x.tree+'-->'+e.ename as varchar(100)),
e.mgr, x.depth+1
from emp e, x
where x.mgr = e.empno
)
select depth, tree
from x
DEPTH TREE
----- ----------------------
0 MILLER
1 MILLER-->CLARK
2 MILLER-->CLARK-->KING
最后,只需要保留层次结构中的最后一行。有多种方法可以实现这一点,但本解决方案通过 DEPTH
来判断何时到达了根节点。(显然,如果 CLARK 的管理者不是 KING,那么基于 DEPTH
的过滤条件可能就不适用。13.3 节提供一种更通用的方法,它不需要上述过滤条件。)
Oracle
对于 Oracle 解决方案而言,CONNECT BY
子句完成了全部的工作。从 MILLER 开始,我们不需要任何连接查询就能一直遍历到 KING。CONNECT BY
子句里的表达式定义了数据之间的关系以及如何遍历树形结构。
select ename
from emp
start with ename = 'MILLER'
connect by prior mgr = empno
ENAME
----------
MILLER
CLARK
KING
关键字 PRIOR
可以访问层次关系中前一条记录的值。因此,对于任意给定的 EMPNO
,可以使用 PRIOR MGR
去获得前一个员工的管理者的 EMPNO
。对于本实例而言,当看到 CONNECT BY PRIOR MGR = EMPNO
这样的子句时,要把它当作是父记录和子记录之间的连接查询。
更多关于
CONNECT BY
及其相关功能的内容,请参考来自 Oracle Technology Network 的文章:“Querying Hierarchies:Top-of-the-Line Support”。
现在,已经成功地显示了从 MILLER 到 KING 的整个层次关系。大部分问题都已经解决了。下面只需格式化即可。使用 SYS_CONNECT_BY_PATH
函数把所有 ENAME
逐一地连接起来。
select sys_connect_by_path(ename,'-->') tree
from emp
start with ename = 'MILLER'
connect by prior mgr = empno
TREE
---------------------------
-->MILLER
-->MILLER-->CLARK
-->MILLER-->CLARK-->KING
因为只希望保留完整的层次关系,我们可以通过伪列 LEVEL
过滤掉不需要的数据(下一个实例将给出一种更加通用的解决方案)。
select sys_connect_by_path(ename,'-->') tree
from emp
where level = 3
start with ename = 'MILLER'
connect by prior mgr = empno
TREE
-----------------------------
-->MILLER-->CLARK-->KING
最后,使用函数 LTRIM
从结果集中删除最前面的 -->
。
PostgreSQL 和 MySQL
因为没有层次查询相关的内置支持,所以我们必须自连接 n 次以返回整个树形结构。(此处的 n 代表叶子节点和根节点之间节点的总个数,包括根节点自身。在本实例中,对于 MILLER 而言,CLARK 是一个分支节点(branch node),而 KING 则是根节点,因此从 MILLER 到 KING 的距离是两个节点,n=2。)本解决方案借用了前一个实例的技巧,只是多了一个自连接。
select a.ename as leaf,
b.ename as branch,
c.ename as root
from emp a, emp b, emp c
where a.ename = 'MILLER'
and a.mgr = b.empno
and b.mgr = c.empno
LEAF BRANCH ROOT
---------- ---------- -----
MILLER CLARK KING
下一步也是最后一步,即格式化输出结果,PostgreSQL 使用||
字符串连接操作符,MySQL 则使用 CONCAT
函数。这一类查询的缺点是,如果层次关系发生了变化,例如,在 CLARK 和 KING 之间多了一个节点,我们就得再增加一个自连接以返回整个树形结构。这就不如有专门的内置函数协助处理层次关系遍历的数据库方便。