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 层的层次关系。有些数据库没有提供能够遍历树形结构数据的函数,我们可以采用 PostgreSQLMySQL 解决方案的做法,但必须再增加一个自连接查询。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

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

Python教程

Java教程

Web教程

数据库教程

图形图像教程

大数据教程

开发工具教程

计算机教程