层次查询

层次查询(CONNECT BY)

 

• 1、层次查询语句
  • 1.1、CONNECT BY 语法
  • 1.2、CONNECT BY 示例
• 2、层次查询函数
  • 2.1、SYS_CONNECT_BY_PATH
  • 2.2、WMSYS.WM_CONCAT
  • 2.3、Oracle 11g 新增的 PIVOT/UNPIVOT
• 3、总结

在我们的日常生活中，有很多数据集合都是有层次结构的，如企业的组织架构、政府机构、家族关系等。为满足对层次化数据的查询及格式化等需求，Oracle 从 9i 开始提供可按层次查询的子句和函数。

1、层次查询语句

1.1、CONNECT BY 语法

基本语法：

[ START WITH condition ] CONNECT BY [ NOCYCLE ] condition

查询语法：

SELECT [LEVEL] [CONNECT_BY_ROOT column] [CONNECT_BY_ISLEAF] [CONNECT_BY_ISCYCLE] column(s),expr(s)
FROM table(s)
[WHERE condition(s)]
[START WITH condition(s)]
CONNECT BY [NOCYCLE] [{ PRIOR column1 = column2 | column1 = PRIOR column2 }]
[ORDER [SIBLINGS] BY column(s)]

• CONNECT BY：该子句表明每行数据都将按层次顺序检索。在层次查询中，CONNECT BY 条件中的一个表达式必须由 PRIOR 运算符限定，从而确定层次结构的父行和子行之间的关系。如果 CONNECT BY 是复合条件，那么只有一个条件需要 PRIOR，尽管可以有多个 PRIOR。PRIOR 可出现在比较运算符的任意一侧，从而确定检索数据的顺序是自上而下还是自下而上（上代指跟节点，下代指叶子节点）。如果 CONNECT BY 的条件是 PRIOR X = Y，那么就会用当前节点的 X 列的值和其它节点的 Y 列的值做比较，值相等的节点就会被当作当前节点的子节点，依此类推。如果 CONNECT BY 后面不加 PRIOR 的话，查询将不进行深层递归。
• START WITH：该子句为可选项，用来标识那个节点作为查找树形结构的根节点。层次查询需要确定起始点，START WITH 后面可以跟任何合法的条件表达式，还可以使用子查询。如果没有 START WITH，则每行都是起始点，进而查找其后代节点。
• LEVEL：一个“伪列”，代表当前节点所在的层级。根节点的 LEVEL 是 1，根节点的子节点的 LEVEL 是 2，依此类推。
• CONNECT_BY_ROOT：一个特殊的“伪列”，特殊之处在于它必须与某个字段搭配使用，用于获取根节点记录的字段信息。
• CONNECT_BY_ISLEAF：一个“伪列”，用于判断当前节点是否为叶子节点，是叶子节点就返回 1，非叶子节点则返回 0。叶子节点是指没有下级节点的节点。
• CONNECT_BY_ISCYCLE：一个特殊的“伪列”，用于检查在执行层次查询过程中是否存在死循环，会造成死循环的行返回 1，其它行则返回 0。该“伪列”只在 CONNECT BY NOCYCLE 方式下有效，默认情况下，Oracle 在运行时检测到无限循环会直接报错。
• SIBLINGS：该关键字放在 ORDER BY 中间，即 ORDER SIBLINGS BY，用于指定结果集中同一个父节点下各个兄弟节点之间的顺序。这么做的好处是不会破坏数据层次，而如果直接在层次查询语句最后面加上 ORDER BY，那么整个结果集都将按 ORDER BY 来排序，这显然会破坏数据层次。

注意：WHERE 条件会限制查询返回的行，但不影响数据的层次关系，不满足条件的节点不返回，但这个不满足条件的节点的后代节点不受此影响。换句话说：WHERE 条件是在遍历结束之后才起作用的，也就是在已经生成的层次数据中进行过滤。

1.2、CONNECT BY 示例

示例 1，列出所有部门自上而下的组织结构层次，并按部门编号在同级部门间排序：

SELECT t.parent_enum_id,t.enum_id,t.enum_code,t.enum_name
FROM demo.t_field_enum t
WHERE t.field_code='DEPT'
CONNECT BY PRIOR t.enum_id=t.parent_enum_id
ORDER SIBLINGS BY t.enum_code;

示例 2，列出软件部门（enum_id=1）自上而下的组织结构层次：

SELECT t.parent_enum_id,t.enum_id,t.enum_code,t.enum_name
FROM demo.t_field_enum t
WHERE t.field_code='DEPT'
START WITH t.enum_id=1
CONNECT BY PRIOR t.enum_id=t.parent_enum_id;

示例 3，CONNECT_BY_ROOT 和 CONNECT_BY_ISLEAF 伪列及 WHERE 过滤的语法演示：

SELECT t.parent_enum_id,t.enum_id,t.enum_code,t.enum_name,CONNECT_BY_ROOT t.enum_id,CONNECT_BY_ISLEAF
FROM demo.t_field_enum t
WHERE t.field_code='DEPT' AND t.enum_id!=7
START WITH t.enum_id=1
CONNECT BY PRIOR t.enum_id=t.parent_enum_id;

示例 4，查找会造成死循环的节点（数据行）：

SELECT t.parent_enum_id,t.enum_id,t.enum_code,t.enum_name
FROM demo.t_field_enum t
WHERE t.field_code='DEPT' AND CONNECT_BY_ISCYCLE=1
START WITH t.enum_id=1
CONNECT BY NOCYCLE PRIOR t.enum_id=t.parent_enum_id;

示例 5，利用 LEVEL 伪列格式化层次查询结果：

SELECT LEVEL lvl,t.enum_id,t.parent_enum_id,t.enum_name,
  RPAD(' ',(LEVEL-1)*4,' ')||t.enum_name format1,
  (CASE (LEVEL-1) WHEN 0 THEN '  ' ELSE (LEVEL-1)||'.' END)||LEVEL||' '||t.enum_name format2
FROM demo.t_field_enum t
WHERE t.field_code='DEPT'
START WITH t.enum_id=1
CONNECT BY NOCYCLE PRIOR t.enum_id=t.parent_enum_id
ORDER SIBLINGS BY t.enum_id;

结果：

   LVL    ENUM_ID PARENT_ENUM_ID ENUM_NAME             FORMAT1                          FORMAT2
------ ---------- -------------- --------------------- -------------------------------- ---------------------------
     1          1              0 软件部                软件部                             1 软件部
     2          7              1 开发部                    开发部                       1.2 开发部
     3         14              7 研发一部                      研发一部                 2.3 研发一部
     3         15              7 研发二部                      研发二部                 2.3 研发二部
     3         16              7 研发三部                      研发三部                 2.3 研发三部
     3         17              7 测试部                        测试部                   2.3 测试部
     2          8              1 工程部                    工程部                       1.2 工程部
     3         18              8 实施一部                      实施一部                 2.3 实施一部
     3         19              8 实施二部                      实施二部                 2.3 实施二部

示例 6，查询数据的层次数：

SELECT COUNT(DISTINCT LEVEL) depth
FROM demo.t_field_enum t
WHERE t.field_code='DEPT'
START WITH t.enum_id=1
CONNECT BY NOCYCLE PRIOR t.enum_id=t.parent_enum_id;

示例 7，查询各级部门的人数及工资之和：

SELECT t3.root_dept_code dept_code,t3.root_dept_name dept_name,
  COUNT(DISTINCT staff_id) cnt_staff,SUM(t3.salary) sum_salary 
FROM(
  SELECT CONNECT_BY_ROOT t1.enum_code root_dept_code,CONNECT_BY_ROOT t1.enum_name root_dept_name,
    t2.staff_id,NVL(t2.base_salary+t2.post_salary,0) salary
  FROM demo.t_field_enum t1
  LEFT JOIN demo.t_staff t2 ON t1.enum_code=t2.dept_code
  WHERE t1.field_code='DEPT'
  CONNECT BY PRIOR t1.enum_id=t1.parent_enum_id
) t3
GROUP BY t3.root_dept_code,t3.root_dept_name
ORDER BY t3.root_dept_code;

结果：

DEPT_CODE                                 DEPT_NAME                                      CNT_STAFF SUM_SALARY
----------------------------------------- --------------------------------------------- ---------- ----------
010000                                    软件部                                                14      99700
010100                                    开发部                                                10      73700
010101                                    研发一部                                               4      29850
010102                                    研发二部                                               2      18500
010103                                    研发三部                                               2      12850
010104                                    测试部                                                 2      12500
010200                                    工程部                                                 4      26000
010201                                    实施一部                                               3      18000
010202                                    实施二部                                               1       8000
020000                                    数据部                                                 0          0
030000                                    市场部                                                 0          0
040000                                    销售部                                                 0          0
050000                                    人事部                                                 0          0
060000                                    财务部                                                 0          0

2、层次查询函数

2.1、SYS_CONNECT_BY_PATH

语法：

SYS_CONNECT_BY_PATH(column, char)

• column：是字符型或能隐式转换成字符型的列，主要作用就是将父节点的路径按照指定的模式出现。
• char：可以是单字符也可以是多字符，但不能使用列值中包含的字符，且必须是常量，也不允许使用绑定变量。

SYS_CONNECT_BY_PATH 函数就是从 START WITH 开始的地方开始遍历，并记录下其遍历到的节点，START WITH 开始的地方被视为跟节点，将遍历到的路径根据函数中指定的分隔符，组成一个新的字符串。需要注意的是该函数仅在层次查询中有效。示例：

SELECT t.enum_id,t.enum_name,SYS_CONNECT_BY_PATH(t.enum_name,'/') format1,SYS_CONNECT_BY_PATH(t.enum_name,' -> ') format2
FROM demo.t_field_enum t
WHERE t.field_code='DEPT'
START WITH t.enum_id=1
CONNECT BY PRIOR t.enum_id=t.parent_enum_id;

结果：

    ENUM_ID ENUM_NAME                FORMAT1                               FORMAT2
----------- ------------------------ ------------------------------------- -----------------------------------
          1 软件部                   /软件部                                -> 软件部
          7 开发部                   /软件部/开发部                         -> 软件部 -> 开发部
         14 研发一部                 /软件部/开发部/研发一部                -> 软件部 -> 开发部 -> 研发一部
         15 研发二部                 /软件部/开发部/研发二部                -> 软件部 -> 开发部 -> 研发二部
         16 研发三部                 /软件部/开发部/研发三部                -> 软件部 -> 开发部 -> 研发三部
         17 测试部                   /软件部/开发部/测试部                  -> 软件部 -> 开发部 -> 测试部
          8 工程部                   /软件部/工程部                         -> 软件部 -> 工程部
         18 实施一部                 /软件部/工程部/实施一部                -> 软件部 -> 工程部 -> 实施一部
         19 实施二部                 /软件部/工程部/实施二部                -> 软件部 -> 工程部 -> 实施二部

2.2、WMSYS.WM_CONCAT

WMSYS.WM_CONCAT 是 Oracle 未公开的一个函数，这意味着在今后的 Oracle 版本中也许将不再可用。官方文档中也完全查不到该函数的任何蛛丝马迹，按常理来说，应该要慎用该函数才是。但据我近几年用 Oracle 以来的观察，似乎每个人都知道 Oracle 中有个 WM_CONCAT 函数，个中缘由我相信用过的人自会知晓。WMSYS.WM_CONCAT 既可用作聚合函数，也可用作分析函数，它的返回值是以逗号分隔的字符串，由满足条件的数据以逗号分隔符串联组成。

示例 1，做聚合函数（无法确保字符串的拼接顺序）：

SELECT t.dept_code,WMSYS.WM_CONCAT(t.staff_name) staff_name_all FROM demo.t_staff t GROUP BY t.dept_code;

结果：

DEPT_CODE                              STAFF_NAME_ALL
-------------------------------------- -------------------------------------------------------------------
010101                                 大海,小明,小强,李阳,王二,大国,小强,王二,小明
010102                                 小林,小林,小萨,小萨
010103                                 韩三,小玲,韩三,小玲
010104                                 小梅,小梅,小燕,小燕
010201                                 小军,小芳,小军,小红,小芳,小红
010202                                 小飞,小飞,徐来

示例 2，做分析函数（可确保字符串按指定顺序拼接）：

WITH t2 AS(
  SELECT t.dept_code,
  WMSYS.WM_CONCAT(t.post_salary) OVER(PARTITION BY t.dept_code ORDER BY t.post_salary) post_salary_all
  FROM demo.t_staff t
)
SELECT t2.dept_code,MAX(t2.post_salary_all) post_salary_all FROM t2 GROUP BY t2.dept_code;

结果：

DEPT_CODE                               POST_SALARY_ALL
--------------------------------------- ----------------------------------------------------------------------
010101                                  1850,1850,3500,4000,6000,6000,8000,8000,8000
010102                                  6000,6000,7500,7500
010103                                  2800,2900,5050,5050
010104                                  3000,3100,4500,4600
010201                                  2000,2100,2500,2600,6000,6000
010202                                  5200,5500,5500

2.3、Oracle 11g 新增的 PIVOT/UNPIVOT

在 Oracle 11g 中新增了两个行列转换函数，分别是行转列函数 PIVOT 和列转行函数 UNPIVOT。我在 11g R2 中测试了一下，感觉还蛮好用的，下面给出我测试的两个 demo。

测试 1，通过 PIVOT 函数实现列转行：

WITH t AS(
  SELECT 1 cid,'苹果' cname, 2000 output FROM DUAL UNION ALL
  SELECT 2 cid,'苹果' cname, 7000 output FROM DUAL UNION ALL
  SELECT 3 cid,'香蕉' cname, 1000 output FROM DUAL UNION ALL
  SELECT 4 cid,'香蕉' cname, 6000 output FROM DUAL UNION ALL
  SELECT 5 cid,'橘子' cname, 5000 output FROM DUAL UNION ALL
  SELECT 6 cid,'西瓜' cname, 8000 output FROM DUAL
)
SELECT * FROM(SELECT t.cname,t.output FROM t) 
  PIVOT(SUM(output) FOR cname IN('苹果' A,'香蕉' B,'橘子' C));

结果：

         A          B          C
---------- ---------- ----------
      9000       7000       5000

测试 2，通过 UNPIVOT 函数实现行转列：

WITH t AS(
  SELECT 1 cid,'苹果' cname, 2000 q1, 4000 q2, 6000 q3, 8000 q4 FROM DUAL UNION ALL
  SELECT 2 cid,'香蕉' cname, 1000 q1, 3000 q2, 5000 q3, 7000 q4 FROM DUAL UNION ALL
  SELECT 3 cid,'橘子' cname, 2000 q1, 5000 q2, 3000 q3, 1000 q4 FROM DUAL UNION ALL
  SELECT 4 cid,'西瓜' cname, 1000 q1, 9000 q2, 1000 q3, 1000 q4 FROM DUAL
)
SELECT cname,quarter,output FROM t UNPIVOT(output FOR quarter IN(q1, q2, q3));

结果：

CNAME QUARTER     OUTPUT
----- ------- ----------
苹果  Q1            2000
苹果  Q2            4000
苹果  Q3            6000
香蕉  Q1            1000
香蕉  Q2            3000
香蕉  Q3            5000
橘子  Q1            2000
橘子  Q2            5000
橘子  Q3            3000
西瓜  Q1            1000
西瓜  Q2            9000
西瓜  Q3            1000

想要进一步了解 PIVOT/UNPIVOT 函数的朋友请参考：pivot and unpivot queries in 11g，尽管不是官方资料，但比官方资料写的要好的多。

3、总结

本文主要讲述了 Oracle 中的层次查询语句 CONNECT BY 及 4 个层次查询函数。以本人的经验来看，掌握这些已经足够应对日常开发中的层次查询需求了。

本文链接：http://www.cnblogs.com/hanzongze/p/Oracle-Connect-By.html