分层查询1、呈现父子关系2、呈现子–父–祖父关系3、创建基于表的分层视图4、找出给定父行的所有子行5、确定叶子节点、分支节点和根节点数据中可能存在层次关系本章介绍表达这种关系的实例。对于层次数据相比于对其进行存储对其进行检索并以层次方式呈现出来通常更难。几年前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.mgrb.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.mgrb.empno;SQL Server自连接 EMP 表然后使用表示拼接运算符的加号​。selecta.ename works for b.enameasemps_and_mgrsfromemp a,emp bwherea.mgrb.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,0fromempwhereenameMILLERunionallselectcast(x.tree--e.enameasvarchar(100)),e.mgr,x.depth1fromemp e,xwherex.mgre.empno)selecttree leaf___branch___rootfromxwheredepth2;只要修改拼接运算符就可以将该解决方案用于其他数据库。换言之用于 DB2 时可以将拼接运算符改为||。MySQL 和 PostgreSQLMySQL 和 PostgreSQL 解决方案与上述解决方案类似只是需要添加关键字 RECURSIVE。WITHRECURSIVE x(tree,mgr,depth)AS(SELECTCAST(enameASCHAR(255)),mgr,0FROMempWHEREenameMILLERUNIONALLSELECTCONCAT(x.tree,--,e.ename),e.mgr,x.depth1FROMemp eJOINxONx.mgre.empno)SELECTtreeASleaf___branch___rootFROMxWHEREdepth2;Oracle使用函数 SYS_CONNECT_BY_PATH 返回 MILLER、MILLER 的管理者 CLARK 以及 CLARK 的管理者KING并使用 CONNECT BY 子句遍历树。selectltrim(sys_connect_by_path(ename,--),--)leaf___branch___rootfromempwherelevel3startwithenameMILLERconnectbyprior mgrempno;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.mgrx.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.mgrx.empno)SELECTenameASemp_treeFROMxORDERBY1;Oracle使用函数 CONNECT BY 定义层次结构并使用函数SYS_CONNECT_BY_PATH 设置输出的格式。selectltrim(sys_connect_by_path(ename, - ), - )emp_treefromempstartwithmgrisnullconnectbyprior empnomgrorderby1;相比于上一节的解决方案该解决方案的不同之处在于没有使用基于伪列 LEVEL 的筛选器。删除这个筛选器后将显示所有可能的树符合条件 PRIOR EMPNOMGR 的树​。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,empnofromempwhereenameJONESunionallselecte.ename,e.empnofromemp e,xwherex.empnoe.mgr)selectenamefromx;Oracle使用 CONNECT BY 子句并指定 START WITH ENAME JONES以找出 JONES 下属的所有员工。selectenamefromempstartwithenameJONESconnectbyprior empnomgr;5、确定叶子节点、分支节点和根节点问题你想判断给定的行是哪种类型的节点叶子节点、分支节点还是根节点。在本实例中叶子节点指的是不是管理者的员工分支节点指的是自己是管理者且还有上级管理者的员工而根节点指的是没有上级管理者的员工。对于层次结构中的每一行你都要返回 1TRUE或 0FALSE​以指出其状态。你希望返回的结果集如下所示。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.mgre.empno))asis_leaf,(selectsign(count(*))fromemp dwhered.mgre.empnoande.mgrisnotnull)asis_branch,(selectsign(count(*))fromemp dwhered.empnoe.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.mgremp.empnoandemp.mgrisnotnullandrownum1)is_branch,decode(ename,connect_by_root(ename),1,0)is_rootfromempstartwithmgrisnullconnectbyprior empnomgrorderby4desc,3desc;