SQL 找出给定的父节点对应的所有子节点|极客教程

SQL 找出给定的父节点对应的所有子节点，你想找出 JONES 的下属员工，既包括直接的下属，也包括间接的下属（即这些员工的管理者是 JONES 的下属）。JONES 的所有下属显示如下。

SQL 找出给定的父节点对应的所有子节点问题描述

你想找出 JONES 的下属员工，既包括直接的下属，也包括间接的下属（即这些员工的管理者是 JONES 的下属）。JONES 的所有下属显示如下。（JONES 本人也会包含在该结果集中。）

ENAME
---------
JONES
SCOTT
ADAMS
FORD
SMITH

SQL 找出给定的父节点对应的所有子节点解决方案

能够自由移动到树形结构的顶端或底端是非常有用的一项特性。本解决方案并不要求做特殊的格式化工作。我们的目标只是要显示 JONES 的所有下属，也包括 JONES 本人。这种类型的查询往往能体现出 SQL 递归特性的优势，例如，Oracle 的 CONNECT BY 子句，SQL Server 和 DB2 的 WITH 子句。
DB2 和 SQL Server
使用 WITH 递归查询找出 JONES 的下属。UNION 查询的前半部分，通过指定 WHERE ENAME = 'JONES' 表明递归操作从 JONES 开始。

 1    with x (ename,empno)
 2      as (
 3  select ename,empno
 4    from emp
 5   where ename = 'JONES'
 6   union all
 7  select e.ename, e.empno
 8    from emp e, x
 9   where x.empno = e.mgr
10  )
11  select ename
12    from x

Oracle
使用 CONNECT BY 子句，并指定 START WITH ENAME = 'JONES' 找出 JONES 的所有下属。

1  select ename
2    from emp
3   start with ename = 'JONES'
4 connect by prior empno = mgr

PostgreSQL 和 MySQL
我们必须先计算出树形结构包含了多少个节点，下面的查询展示了如何确定层次关系的深度。

/* 找到JONES的EMPNO */
select ename,empno,mgr
  from emp
 where ename = 'JONES'
　
ENAME           EMPNO        MGR
---------- ---------- ----------
JONES            7566       7839
　
/* JONES有直接下属吗？ */
select count(*)
  from emp
 where mgr = 7566
　
  COUNT(*)
----------
         2
　
/* JONES有两个直接下属，找到他们的EMPNO */
select ename,empno,mgr
  from emp
 where mgr = 7566
　
ENAME           EMPNO        MGR
---------- ---------- ----------
SCOTT            7788       7566
FORD             7902       7566
　
/* SCOTT和FORD有下属吗？ */
select count(*)
  from emp
 where mgr in (7788,7902)
　
  COUNT(*)
----------
         2
　
/* SCOTT和FORD各有一个下属，找出他们的EMPNO */
select ename,empno,mgr
  from emp
 where mgr in (7788,7902)
　
ENAME           EMPNO        MGR
---------- ---------- ----------
SMITH            7369       7902
ADAMS            7876       7788
　
/* SMITH和ADAMS有下属吗？ */
select count(*)
  from emp
 where mgr in (7369,7876)
　
  COUNT(*)
----------
         0

该层次关系起始于 JONES，终止于 SMITH 和 ADAMS。它的深度为 3 层。既然已经知道了深度，我们就可以从顶端到底端依次遍历整个层次关系。
首先，2 次自连接 EMP 表。然后转换内嵌视图 X 把 2 行 3 列的数据集变为 6 行 1 列。（对于 PostgreSQL 而言，还有另一种做法，即可以使用 GENERATE_SERIES(1,6) 来代替数据透视表 T100。）

 1  select distinct
 2         case t100.id
 3              when 1 then root
 4              when 2 then branch
 5              else        leaf
 6         end as JONES_SUBORDINATES
 7    from (
 8  select a.ename as root,
 9         b.ename as branch,
10         c.ename as leaf
11    from emp a, emp b, emp c
12   where a.ename = 'JONES'
13     and a.empno = b.mgr
14     and b.empno = c.mgr
15         ) x,
16         t100
17   where t100.id <= 6

除此之外，我们还可以使用视图，并把视图的结果集 UNION 起来。我们创建了一些视图，如下所示。

create view v1
as
select ename,mgr,empno
  from emp
 where ename = 'JONES'
　
create view v2
as
select ename,mgr,empno
  from emp
 where mgr = (select empno from v1)
　
create view v3
as
select ename,mgr,empno
  from emp
 where mgr in (select empno from v2)

因而，本解决方案将就变成了下面的查询。

select ename from v1
 union
select ename from v2
 union
select ename from v3

SQL 找出给定的父节点对应的所有子节点扩展知识

DB2 和 SQL Server
WITH 递归查询使得本问题解决起来容易多了。WITH 子句的第一部分，即 UNION ALL 的前半部分，返回了员工 JONES 所在的行。我们需要返回 ENAME 以得到员工的名字，并返回 EMPNO 以便基于它做连接查询。UNION ALL 的后半部分基于 EMP.MGR 和 X.EMPNO 做递归的连接查询。该连接条件将一次次地执行下去，直至遍历完整个结果集。
Oracle
START WITH 子句表明查询操作将以 JONES 为根节点。CONNECT BY 子句的条件驱动树形结构的遍历操作，并将一直执行下去，直到条件不再成立才会停止。
PostgreSQL 和 MySQL
本解决方案最显而易见的不足是，我们需要提前知道层次关系的深度。