SQL 展现祖孙关系|极客教程

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 之间多了一个节点，我们就得再增加一个自连接以返回整个树形结构。这就不如有专门的内置函数协助处理层次关系遍历的数据库方便。