SQL经典实例——分层查询

数据中可能存在层次关系，本章介绍表达这种关系的实例。对于层次数据，相比于对其进行存储，对其进行检索并以层次方式呈现出来通常更难。

几年前，MySQL 引入了递归式 CTE，现在大多数RDBMS 支持这种功能。因此，使用递归式 CTE 已成为编写分层查询的标准方法。

先来看看 EMP 表中 EMPNO 和 MGR 之间的层次关系。

selectempno,mgrfromemporderby2;empno|mgr-------+------7902|75667788|75667521|76987844|76987654|76987900|76987499|76987934|77827876|77887782|78397698|78397566|78397369|79027839|(14rows)

如果仔细观察，你将发现每个 MGR 值都是一个 EMPNO，这意味着 EMP 表中的每位管理者也同样是员工，且未被存储在其他地方。MGR 和 EMPNO 之间为父子关系，因为EMPNO 对应的 MGR 值是它的直接父节点。​（对于特定的员工，其管理者之上可能还有管理者，而这些管理者之上也有管理者，以此类推，形成 n 层层次结构。​）对于没有管理者的员工，其 MGR 值为 NULL。

1、呈现父子关系

问题：你想在返回子记录中数据的同时，返回父记录中的信息。例如，你想显示每位员工的名字以及其管理者的名字。换言之，你想返回如下结果集。

EMPS_AND_MGRS------------------------------FORD worksforJONES SCOTT worksforJONES JAMES worksforBLAKE TURNER worksforBLAKE MARTIN worksforBLAKE WARD worksforBLAKE ALLEN worksforBLAKE MILLER worksforCLARK ADAMS worksforSCOTT CLARK worksforKING BLAKE worksforKING JONES worksforKING SMITH worksforFORD

解决方案：基于 MGR 和 EMPNO 相等自连接 EMP 表，以找出每位员工的管理者的名字。然后，使用 RDBMS 提供的字符串拼接函数生成所需的字符串。

DB2、Oracle 和 PostgreSQL：自连接 EMP 表，然后使用表示拼接运算符的双竖线（||）​。

selecta.ename||' works for '||b.enameasemps_and_mgrsfromemp a,emp bwherea.mgr=b.empno;emps_and_mgrs------------------------SMITH worksforFORD ALLEN worksforBLAKE WARD worksforBLAKE JONES worksforKING MARTIN worksforBLAKE BLAKE worksforKING CLARK worksforKING SCOTT worksforJONES TURNER worksforBLAKE ADAMS worksforSCOTT JAMES worksforBLAKE FORD worksforJONES MILLER worksforCLARK(13rows)

MySQL：自连接 EMP 表，然后使用拼接函数 CONCAT。

selectconcat(a.ename,' works for ',b.ename)asemps_and_mgrsfromemp a,emp bwherea.mgr=b.empno;

SQL Server：自连接 EMP 表，然后使用表示拼接运算符的加号（+）​。

selecta.ename+' works for '+b.enameasemps_and_mgrsfromemp a,emp bwherea.mgr=b.empno;

2、呈现子–父–祖父关系

问题：员工 CLARK 是 KING 的下属，要表示这种关系，可以使用上一节中的解决方案。如果员工 CLARK 还是另一位员工的管理者，那么该如何表示这种关系呢？请看下面的查询。

selectename,empno,mgrfromempwhereenamein('KING','CLARK','MILLER');ENAME EMPNO MGR--------- -------- -------CLARK77827839KING7839MILLER79347782

如你所见，员工 MILLER 是 CLARK 的下属，而 CLARK是 KING 的下属。你要呈现从 MILLER 到 KING 的完整层次结构。换言之，你想返回如下结果集。

LEAF___BRANCH___ROOT---------------------MILLER-->CLARK-->KING

然而，上一节使用的单次自连接方法无法呈现上述完整关系。虽然可以编写执行两次自连接的查询，但使用遍历层次结构的通用方法更佳。

解决方案：本实例不同于上一个实例，因为它要呈现的关系包含 3层。Oracle 提供了遍历树型数据的功能，如果你使用的 RDBMS 没有提供这种功能，则可以使用 CTE 来解决这个问题。

DB2 和 SQL Server：使用递归式 WITH 找出 MILLER 的管理者 CLARK，再找出 CLARK 的管理者 KING。下面的解决方案使用的是SQL Server 字符串拼接运算符 +。

withx(tree,mgr,depth)as(selectcast(enameasvarchar(100)),mgr,0fromempwhereename='MILLER'unionallselectcast(x.tree+'-->'+e.enameasvarchar(100)),e.mgr,x.depth+1fromemp e,xwherex.mgr=e.empno)selecttree leaf___branch___rootfromxwheredepth=2;

只要修改拼接运算符，就可以将该解决方案用于其他数据库。换言之，用于 DB2 时，可以将拼接运算符改为||。

MySQL 和 PostgreSQL：MySQL 和 PostgreSQL 解决方案与上述解决方案类似，只是需要添加关键字 RECURSIVE。

WITHRECURSIVE x(tree,mgr,depth)AS(SELECTCAST(enameASCHAR(255)),mgr,0FROMempWHEREename='MILLER'UNIONALLSELECTCONCAT(x.tree,'-->',e.ename),e.mgr,x.depth+1FROMemp eJOINxONx.mgr=e.empno)SELECTtreeASleaf___branch___rootFROMxWHEREdepth=2;

Oracle：使用函数 SYS_CONNECT_BY_PATH 返回 MILLER、MILLER 的管理者 CLARK 以及 CLARK 的管理者KING，并使用 CONNECT BY 子句遍历树。

selectltrim(sys_connect_by_path(ename,'-->'),'-->')leaf___branch___rootfromempwherelevel=3startwithename='MILLER'connectbyprior mgr=empno;

3、创建基于表的分层视图

问题：你想返回一个结果集，将整张表的层次结构呈现出来。在EMP 表中，员工 KING 之上没有管理者，因此 KING 为根节点。你想从 KING 开始，显示其所有下属以及这些下属的所有下属。换言之，你想返回如下结果集。

EMP_TREE------------------------------KING KING-BLAKE KING-BLAKE-ALLEN KING-BLAKE-JAMES KING-BLAKE-MARTIN KING-BLAKE-TURNER KING-BLAKE-WARD KING-CLARK KING-CLARK-MILLER KING-JONES KING-JONES-FORD KING-JONES-FORD-SMITH KING-JONES-SCOTT KING-JONES-SCOTT-ADAMS

解决方案：

DB2、PostgreSQL 和 SQL Server：使用递归式 WITH 子句生成一个层次结构，其中包含KING 及其管理的所有员工。下面展示的是 DB2 解决方案（使用的是 DB2 拼接运算符 ||）​。要将该解决方案用于 SQL Server 和 MySQL，只需在其中分别使用拼接运算符 + 和拼接函数 CONCAT。

withRECURSIVE x(ename,empno)as(selectcast(enameasvarchar(100)),empnofromempwheremgrisnullunionallselectcast(x.ename||' - '||e.enameasvarchar(100)),e.empnofromemp e,xwheree.mgr=x.empno)selectenameasemp_treefromxorderby1;emp_tree------------------------------KING KING-BLAKE KING-BLAKE-ALLEN KING-BLAKE-JAMES KING-BLAKE-MARTIN KING-BLAKE-TURNER KING-BLAKE-WARD KING-CLARK KING-CLARK-MILLER KING-JONES KING-JONES-FORD KING-JONES-FORD-SMITH KING-JONES-SCOTT KING-JONES-SCOTT-ADAMS(14rows)

MySQL：在 MySQL 中，还需添加关键字 RECURSIVE。

WITHRECURSIVE x(ename,empno)AS(SELECTCAST(enameASCHAR(100)),empnoFROMempWHEREmgrISNULLUNIONALLSELECTCAST(CONCAT(x.ename,' - ',e.ename)ASCHAR(255)),e.empnoFROMemp eJOINxONe.mgr=x.empno)SELECTenameASemp_treeFROMxORDERBY1;

Oracle：使用函数 CONNECT BY 定义层次结构，并使用函数SYS_CONNECT_BY_PATH 设置输出的格式。

selectltrim(sys_connect_by_path(ename,' - '),' - ')emp_treefromempstartwithmgrisnullconnectbyprior empno=mgrorderby1;

相比于上一节的解决方案，该解决方案的不同之处在于没有使用基于伪列 LEVEL 的筛选器。删除这个筛选器后，将显示所有可能的树（符合条件 PRIOR EMPNO=MGR 的树）​。

4、找出给定父行的所有子行

问题：你想找出 JONES 的所有下属，包括直接下属和间接下属（JONES 的下属的下属）​。下面列出了 JONES 及其所有下属。

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

解决方案：能够定位到树的顶部或底部很有用。在本解决方案中，不需要特殊的格式设置。这里的目标很简单，就是返回JONES 下属的所有员工，包括 JONES 自己。这种查询充分展示了递归式 SQL 扩展（比如 Oracle 的 CONNECTBY 以及 SQL Server 和 DB2 的 WITH 子句）的威力。
DB2、PostgreSQL 和 SQL Server：使用递归式 WITH 子句找出是 JONES 下属的所有员工。从 JONES 开始，在 UNION ALL 上半部分的查询中指定WHERE ENAME = JONES。

withx(ename,empno)as(selectename,empnofromempwhereename='JONES'unionallselecte.ename,e.empnofromemp e,xwherex.empno=e.mgr)selectenamefromx;

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

selectenamefromempstartwithename='JONES'connectbyprior empno=mgr;

5、确定叶子节点、分支节点和根节点

问题：你想判断给定的行是哪种类型的节点：叶子节点、分支节点还是根节点。在本实例中，叶子节点指的是不是管理者的员工，分支节点指的是自己是管理者且还有上级管理者的员工，而根节点指的是没有上级管理者的员工。对于层次结构中的每一行，你都要返回 1（TRUE）或 0（FALSE）​，以指出其状态。你希望返回的结果集如下所示。

ENAME IS_LEAF IS_BRANCH IS_ROOT---------- ---------- ---------- ----------KING001JONES010SCOTT010FORD010CLARK010BLAKE010ADAMS100MILLER100JAMES100TURNER100ALLEN100WARD100MARTIN100SMITH100

解决方案：EMP 表建立的是树型层次结构，而不是递归层次结构，因为根节点的 MGR 为 NULL，认识到这一点很重要。如果EMP 建立的是递归层次结构，那么根节点将指向自己（也就是说，员工 KING 的 MGR 值将为他的 EMPNO）​。我们发现，指向自己是不合常理的，因此将根节点的 MGR 设置为了 NULL。使用 Oracle 的 CONNECT BY 以及 DB2和 SQL Server 的 WITH 子句时，你会发现树型层次结构比递归层次结构更容易处理，效率也更高。使用CONNECT BY 或 WITH 处理递归层次结构时务必小心，因为最终编写的 SQL 代码可能包含循环。如果处理递归层次结构时出现问题，那么请务必检查这种循环。

DB2、PostgreSQL、MySQL 和 SQL Server：使用 3 个标量子查询在每个节点类型列中返回正确的“布尔”值（1 或 0）​。

selecte.ename,(selectsign(count(*))fromemp dwhere0=(selectcount(*)fromemp fwheref.mgr=e.empno))asis_leaf,(selectsign(count(*))fromemp dwhered.mgr=e.empnoande.mgrisnotnull)asis_branch,(selectsign(count(*))fromemp dwhered.empno=e.empnoandd.mgrisnull)asis_rootfromemp eorderby4desc,3desc;

Oracle：上述子查询解决方案也适用于 Oracle。如果你使用的是Oracle Database 10g 以前的版本，那么也应该使用这种解决方案。下面的解决方案使用了 Oracle 提供的内置函数 CONNECT_BY_ROOT 和 CONNECT_BY_ISLEAF（这些内置函数是 Oracle Database 10g 引入的）来找出根行和叶子行。

selectename,connect_by_isleaf is_leaf,(selectcount(*)fromemp ewheree.mgr=emp.empnoandemp.mgrisnotnullandrownum=1)is_branch,decode(ename,connect_by_root(ename),1,0)is_rootfromempstartwithmgrisnullconnectbyprior empno=mgrorderby4desc,3desc;