《SQL 烹饪书》第二版

第 13 章 层次查询 分层查询

本作品已使用人工智能进行翻译。欢迎您提供反馈和意见：[e-mail]

本章介绍表达数据中可能存在的层次关系的方法。在处理层次结构数据时，检索和显示数据（作为层次结构）通常比存储数据更困难。

尽管 MySQL 添加递归 CTE 只有短短几年时间，但既然递归 CTE 已经可用，就意味着几乎所有 RDBMS 都可以使用递归 CTE。因此，递归 CTE 是处理分层查询的黄金标准，本章将充分利用这一功能，提供帮助你解开数据分层结构的秘诀。

在开始之前，请检查表 EMP 以及 EMPNO 和 MGR 之间的层次关系：

select empno,mgr
  from emp
order by 2

     EMPNO        MGR
---------- ----------
      7788       7566
      7902       7566
      7499       7698
      7521       7698
      7900       7698
      7844       7698
      7654       7698
      7934       7782
      7876       7788
      7566       7839
      7782       7839
      7698       7839
      7369       7902
      7839

如果仔细观察，就会发现 MGR 的每个值也是 EMPNO，这意味着表 EMP 中每个雇员的经理也是表 EMP 中的雇员，而不是存储在其他地方。MGR 和 EMPNO 之间的关系是父子关系，即 MGR 的值是给定 EMPNO 的最直接父级（也有可能某个雇员的经理也有一个经理，这些经理又有经理，以此类推，形成一个n 层的层次结构）。如果雇员没有经理，则 MGR 为空。

13.1 表达亲子关系

问题

您希望在显示子记录数据的同时，也显示父记录的信息。例如，您想显示每位员工的姓名及其经理的姓名。您希望返回以下结果集：

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

解决方案

在 MGR 和 EMPNO 上自连接 EMP，以查找每位员工的经理姓名。然后使用 RDBMS 提供的字符串连接功能，在所需的结果集中生成字符串。

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 服务器

自连接 EMP。然后使用加号 (+) 作为连接运算符： ...