DB2 10.5 for Linux, UNIX, and Windows
分层查询
分层查询是一种递归查询形式，它使用 CONNECT BY 子句从关系数据中检索层次结构，例如材料清单。
启用
db2set DB2_COMPATIBILITY_VECTOR=08
db2stop
db2start然后您可以使用 CONNECT BY 语法，包括伪列、一元运算符和 SYS_CONNECT_BY_PATH 标量函数。
分层查询包含 CONNECT BY 子句，该子句定义父元素和子元素之间的连接条件。“连接”递归将同一个子查询用于种子值（START WITH 子句）和递归步骤（CONNECT BY 子句）。此组合提供了一种简明的方法来表示递归，例如材料清单、报告链或电子邮件线程。
出现循环时，“连接”递归将返回错误。循环是指，某一行直接或间接地生成其自身。通过使用可选的 CONNECT BY NOCYCLE 子句，可以指示递归忽略重复的行，从而避免循环和错误。分层查询或“连接”递归不同于 DB2 递归。有关这些差异的更多信息，请参阅将 CONNECT BY 移植到 DB2。
分层查询子句
包含分层查询子句的子选择被称为“分层查询”。
>>-●--+-------------------+--●--| CONNECT BY 子句 |--●----------->< '-| START WITH 子句 |-' START WITH 子句 |--START WITH--搜索条件---------------------------------------------| CONNECT BY 子句 |--CONNECT BY--+---------+--搜索条件--------------------------------| '-NOCYCLE-'
- START WITH 子句
- START WITH 表示递归的种子值。START WITH 子句指定分层查询的中间结果表 H1。表 H1 包含 R 中搜索条件为 true 的那么行。如果未指定 START WITH 子句，那么 H1 将是整个中间结果表 R。START WITH 子句 中搜索条件的规则与 WHERE 子句中的规则相同。
- CONNECT BY 子句
- CONNECT BY 描述了该递归步骤。CONNECT BY 子句通过将 Hn 与 R 连接，使用搜索条件来根据 Hn 生成中间结果表 Hn+1。 如果指定 NOCYCLE 关键字，那么重复行不会包括在中间结果表 Hn+1 中。不会返回错误。CONNECT BY 子句中搜索条件的规则与 WHERE 子句的规则相同，除非无法指定 OLAP 规范 (SQLSTATE 42903)。
建立第一个中间结果表 H1 之后，将生成后续中间结果表 H2、H3 等等。通过使用 CONNECT BY 子句作为连接条件将 Hn 与表 R 进行连接生成 Hn+1，从而生成后续创建的中间结果表。R 是子选择的 FROM 子句以及 WHERE 子句中任何连接谓词的结果。当 Hn+1 生成空结果表时，此过程停止。只要 UNION ALL 适用于每个中间结果表，那么分层查询子句的结果表 H 是该结果。
CONNECT BY MGRID = PRIOR EMPID规则
- 如果中间结果表 Hn+1 对于某分层路径将返回 R 中的某一行，而该行与 R 中已包含在该分层路径中的另一行相同，那么将返回错误 (SQLSTATE 560CO)。
- 如果指定了 NOCYCLE 关键字，那么不会返回错误，但重复的行不会包括在中间结果表 Hn+1 中。
- 最多支持 64 级递归 (SQLSTATE 54066)。
- 作为分层查询的子选择按不完整顺序返回中间结果集，除非您通过使用显式的 ORDER BY 子句、GROUP BY 或 HAVING 子句或者选择列表中的 DISTINCT 关键字来破坏该顺序。按不完整顺序返回行时，在 Hn+1 中为特定层次结构生成的行将紧跟在生成那些行的 Hn 中的行之后。您可以使用 ORDER SIBLINGS BY 子句在同一父级所生成的一组行中强制实施顺序。
- 具体化查询表不支持分层查询 (SQLSTATE 428EC)。
- 无法将 CONNECT BY 子句与 XML 函数或 XQuery 配合使用 (SQLSTATE 428H4)。
- 无法在以下位置中对序列指定 NEXT VALUE 表达式 (SQLSTATE 428F9)：
- CONNECT_BY_ROOT 运算符或 SYS_CONNECT_BY_PATH 函数的参数列表
- START WITH 和 CONNECT BY 子句
备注
- 分层查询支持在以下方面影响子选择：
- 按如下顺序处理子选择的子句：
- FROM 子句
- 分层查询子句
- WHERE 子句
- GROUP BY 子句
- HAVING 子句
- SELECT 子句
- ORDER BY 子句
- FETCH FIRST 子句
- 特殊规则适用于在 WHERE 子句中处理谓词的顺序。搜索条件将与其
AND 条件（合取）一起包括到谓词中。如果某个谓词是隐式连接谓词（即，它引用了 FROM
子句中的多个表），那么将在应用分层查询子句之前应用该谓词。任何最多引用了
FROM 子句中的一个表的谓词都将应用于分层查询子句的中间结果表。
如果编写涉及连接的分层查询，那么使用显式的连接表通过 ON 子句来避免产生与 WHERE 子句谓词的应用相关的混淆。
- 可以指定 ORDER SIBLINGS BY 子句。此子句指定顺序仅应用于层次结构中的胞代。
- 按如下顺序处理子选择的子句：
- 伪列是限定的标识或未限定的标识，它在特定上下文中具有含义，并与某些列和变量共享同一个名称空间。如果未限定的标识未标识列或变量，那么检查该标识以确定它是否标识了伪列。
LEVEL 是一个供分层查询使用的伪列。LEVEL 伪列返回层次结构中生成行的递归步骤。所有由 START WITH 子句生成的行都将返回值 1。通过应用 CONNECT BY 子句的第一个迭代生成的行将返回 2，依此类推。此列的数据类型是 INTEGER NOT NULL。
必须在分层查询的上下文中指定 LEVEL。无法在 START WITH 子句中指定 LEVEL 作为 CONNECT_BY_ROOT 运算符的参数或作为 SYS_CONNECT_BY_PATH 函数的参数 (SQLSTATE 428H4)。
- 支持分层查询的一元运算符是 CONNECT_BY_ROOT 和 PRIOR。
- 支持分层查询的函数是 SYS_CONNECT_BY_PATH 标量函数。
示例
- 以下报告链示例演示“连接”递归。此示例基于名为 MY_EMP 的表，该表是使用如下数据来创建和填充的：
CREATE TABLE MY_EMP( EMPID INTEGER NOT NULL PRIMARY KEY, NAME VARCHAR(10), SALARY DECIMAL(9, 2), MGRID INTEGER); INSERT INTO MY_EMP VALUES ( 1, 'Jones', 30000, 10); INSERT INTO MY_EMP VALUES ( 2, 'Hall', 35000, 10); INSERT INTO MY_EMP VALUES ( 3, 'Kim', 40000, 10); INSERT INTO MY_EMP VALUES ( 4, 'Lindsay', 38000, 10); INSERT INTO MY_EMP VALUES ( 5, 'McKeough', 42000, 11); INSERT INTO MY_EMP VALUES ( 6, 'Barnes', 41000, 11); INSERT INTO MY_EMP VALUES ( 7, 'O''Neil', 36000, 12); INSERT INTO MY_EMP VALUES ( 8, 'Smith', 34000, 12); INSERT INTO MY_EMP VALUES ( 9, 'Shoeman', 33000, 12); INSERT INTO MY_EMP VALUES (10, 'Monroe', 50000, 15); INSERT INTO MY_EMP VALUES (11, 'Zander', 52000, 16); INSERT INTO MY_EMP VALUES (12, 'Henry', 51000, 16); INSERT INTO MY_EMP VALUES (13, 'Aaron', 54000, 15); INSERT INTO MY_EMP VALUES (14, 'Scott', 53000, 16); INSERT INTO MY_EMP VALUES (15, 'Mills', 70000, 17); INSERT INTO MY_EMP VALUES (16, 'Goyal', 80000, 17); INSERT INTO MY_EMP VALUES (17, 'Urbassek', 95000, NULL);以下查询返回所有为 Goyal 工作的职员以及某些其他信息，例如报告链：1 SELECT NAME, 2 LEVEL, 3 SALARY, 4 CONNECT_BY_ROOT NAME AS ROOT, 5 SUBSTR(SYS_CONNECT_BY_PATH(NAME, ':'), 1, 25) AS CHAIN 6 FROM MY_EMP 7 START WITH NAME = 'Goyal' 8 CONNECT BY PRIOR EMPID = MGRID 9 ORDER SIBLINGS BY SALARY;NAME LEVEL SALARY ROOT CHAIN ---------- ----------- ----------- ----- --------------- Goyal 1 80000.00 Goyal :Goyal Henry 2 51000.00 Goyal :Goyal:Henry Shoeman 3 33000.00 Goyal :Goyal:Henry:Shoeman Smith 3 34000.00 Goyal :Goyal:Henry:Smith O'Neil 3 36000.00 Goyal :Goyal:Henry:O'Neil Zander 2 52000.00 Goyal :Goyal:Zander Barnes 3 41000.00 Goyal :Goyal:Zander:Barnes McKeough 3 42000.00 Goyal :Goyal:Zander:McKeough Scott 2 53000.00 Goyal :Goyal:Scott第 7 和第 8 行组成递归核心：可选的 START WITH 子句描述要用于源表以作为递归种子值的 WHERE 子句。在本例中，将只选择职员 Goyal 的行。如果省略 START WITH 子句，那么将使用整个源表作为递归种子值。CONNECT BY 子句描述在给定现有行的情况下如何找到下一组行。一元运算符 PRIOR 用于对上一步骤中的值与当前步骤中的值进行区分。PRIOR 将 EMPID 标识为上一递归步骤的职员标识，并将 MGRID 标识为来自当前递归步骤。
行 2 中的 LEVEL 伪列指示当前的递归级别。
CONNECT_BY_ROOT 是一元运算符，它始终返回其自变量在第一个递归步骤中的值；即，显式或隐式 START WITH 子句所返回的值。
SYS_CONNECT_BY_PATH() 是二进制函数，它将第二个自变量添加到第一个自变量开头，然后将结果追加到它在上一递归步骤中生成的值末尾。自变量必须具有字符类型。
除非被明确覆盖，否则“连接”递归将按不完整顺序来返回结果集；即，某个递归步骤所生成的行始终跟在生成它们的行之后。处于同一递归级别的胞代没有特定的顺序。第 9 行的 ORDER SIBLINGS BY 子句定义这些胞代的顺序，这将进一步优化不完整顺序，并有可能形成完整顺序。
- 返回 DEPARTMENT 表的组织结构。使用部门级别将层次结构可视化。
此查询返回：SELECT LEVEL, CAST(SPACE((LEVEL - 1) * 4) || '/' || DEPTNAME AS VARCHAR(40)) AS DEPTNAME FROM DEPARTMENT START WITH DEPTNO = 'A00' CONNECT BY NOCYCLE PRIOR DEPTNO = ADMRDEPTLEVEL DEPTNAME ----------- ---------------------------------------- 1 /SPIFFY COMPUTER SERVICE DIV. 2 /PLANNING 2 /INFORMATION CENTER 2 /DEVELOPMENT CENTER 3 /MANUFACTURING SYSTEMS 3 /ADMINISTRATION SYSTEMS 2 /SUPPORT SERVICES 3 /OPERATIONS 3 /SOFTWARE SUPPORT 3 /BRANCH OFFICE F2 3 /BRANCH OFFICE G2 3 /BRANCH OFFICE H2 3 /BRANCH OFFICE I2 3 /BRANCH OFFICE J2