SQL 展现父子关系|极客教程

SQL 展现父子关系，你想找出子节点对应的父节点信息。例如，你希望显示每个员工及其管理者的名字。你希望返回的结果集如下所示。

SQL 展现父子关系问题描述

你想找出子节点对应的父节点信息。例如，你希望显示每个员工及其管理者的名字。你希望返回的结果集如下所示。

EMPS_AND_MGRS
------------------------------
FORD works for JONES
SCOTT works for JONES
JAMES works for BLAKE
TURNER works for BLAKE
MARTIN works for BLAKE
WARD works for BLAKE
ALLEN works for BLAKE
MILLER works for CLARK
ADAMS works for SCOTT
CLARK works for KING
BLAKE works for KING
JONES works for KING
SMITH works for FORD

SQL 展现父子关系解决方案

基于 MGR 和 EMPNO 对 EMP 表做自连接查询，找出每个员工的管理者的名字。然后使用数据库提供的字符串连接函数生成符合要求的字符串。
DB2、Oracle 和 PostgreSQL
自连接 EMP 表。然后使用双竖线||连接字符串。

1 select a.ename || ' works for ' || b.ename as emps_and_mgrs
2   from emp a, emp b
3  where a.mgr = b.empno

MySQL
自连接 EMP 表，然后使用 CONCAT 函数连接字符串。

1 select concat(a.ename, ' works for ',b.ename) as emps_and_mgrs
2   from emp a, emp b
3  where a.mgr = b.empno

SQL Server
自连接 EMP 表，然后使用加号“+”连接字符串。

1 select a.ename + ' works for ' + b.ename as emps_and_mgrs
2   from emp a, emp b
3  where a.mgr = b.empno

SQL 展现父子关系扩展知识

以上所有解决方案的实现思路大致相同。不同之处仅在于字符串连接的方式，所以下面的讨论能够兼顾到所有的解决方案，不用再一一解释每一个具体的做法。
基于 MGR 和 EMPNO 的连接查询是关键所在。首先通过自连接 EMP 表创建一个笛卡儿积（下面只显示了笛卡儿积返回值的一部分）。

select a.empno, b.empno
  from emp a, emp b
　
EMPNO        MGR
----- ----------
 7369       7369
 7369       7499
 7369       7521
 7369       7566
 7369       7654
 7369       7698
 7369       7782
 7369       7788
 7369       7839
 7369       7844
 7369       7876
 7369       7900
 7369       7902
 7369       7934
 7499       7369
 7499       7499
 7499       7521
 7499       7566
 7499       7654
 7499       7698
 7499       7782
 7499       7788
 7499       7839
 7499       7844
 7499       7876
 7499       7900
 7499       7902
 7499       7934

如上所示，笛卡儿积返回了每一种可能的 EMPNO/EMPNO 组合。（这样看起来所有人都是 EMPNO 7369 的管理者，甚至也包括 7369 本人。）
下一步要过滤结果集，以便只返回每个员工及其管理者的 EMPNO。增加 MGR 和 EMPNO 相等的判断条件即可。

1 select a.empno, b.empno mgr
2    from emp a, emp b
3   where a.mgr = b.empno
　
     EMPNO        MGR
---------- ----------
      7902       7566
      7788       7566
      7900       7698
      7844       7698
      7654       7698
      7521       7698
      7499       7698
      7934       7782
      7876       7788
      7782       7839
      7698       7839
      7566       7839
      7369       7902

现在已经得到了每个员工及其管理者的 EMPNO，接下来只要查询 B.ENAME 提取出每个管理者的名字即可。如果你还没有完全理解本解决方案的原理，那么可以先忽略自连接方案，转而尝试下面的标量子查询方案。

select a.ename,
       (select b.ename
          from emp b
         where b.empno = a.mgr) as mgr
  from emp a
　
ENAME      MGR
---------- ----------
SMITH      FORD
ALLEN      BLAKE
WARD       BLAKE
JONES      KING
MARTIN     BLAKE
BLAKE      KING
CLARK      KING
SCOTT      JONES
KING
TURNER     BLAKE
ADAMS      SCOTT
JAMES      BLAKE
FORD       JONES
MILLER     CLARK

上述标量子查询方案实际上等价于自连接方案，只有一行数据除外：员工 KING 出现在了结果集里，而自连接方案的查询结果里却不包括该员工。你可能会问：“为什么不会包括？”注意，Null 不等于任何值，甚至不等于它自身。在自连接解决方案中，我们基于 EMPNO 和 MGR 做相等连接查询（equi-join），这就剔除了 MGR 值等于 Null 的员工。如果既使用自连接方案，又希望员工 KING 出现在最终的结果集里，就必须使用外连接，就像下面的两种查询那样。第一种解决方案使用了 ANSI 外连接，第二种则用了 Oracle 的外连接语法。这两种解决方案的结果自然是相同的，我们把输出结果放在了第二个方案的后面。

/* ANSI */
select a.ename, b.ename mgr
  from emp a left join emp b
    on (a.mgr = b.empno)
　
/* Oracle */
select a.ename, b.ename mgr
  from emp a, emp b
 where a.mgr = b.empno (+)
　
ENAME      MGR
---------- ----------
FORD       JONES
SCOTT      JONES
JAMES      BLAKE
TURNER     BLAKE
MARTIN     BLAKE
WARD       BLAKE
ALLEN      BLAKE
MILLER     CLARK
ADAMS      SCOTT
CLARK      KING
BLAKE      KING
JONES      KING
SMITH      FORD
KING