3. Oracle 层次查询
(本文档是按照官方19c进行编写，但实际测试是在11g上)
3.1 描述
Oracle层次化查询是Oracle特有的功能实现,主要用于返回一个数据集,这个数据集存在树的关系(数据集中存在一个Pid记录着当前数据集某一条记录的Id)
层次化查询主要包含两个子句,一个start with另一个是connect by
start with: 这个子句一般用于指定层次化查询的开始节点(也就是树的最顶级节点),找到最顶级节点,然后按照一定的规则开始查找其剩余的子节点
connect by: 这个子句就是上面所说的规则,用于查找剩余子节点的规则
start with 和 connect by 顺序不敏感,且当 connect by 在前时可以忽略 start with
-- 表
-- ID 代表员工id
-- PARENTID 代表上属员工id
-- UNAME 代表员工名称
-- IS_ENABLED 代表该员工是否生效，目前用'Y', 'N'区分
CREATE TABLE stuff
( "ID" VARCHAR2(20),
"PARENTID" VARCHAR2(20),
"UNAME" VARCHAR2(50),
"IS_ENABLED" VARCHAR2(1)
);
--代表获取工号为0001的下属所有员工名字以及0001本身的名字
SELECT '0001' AS ID
, UNAME AS NAME
FROM stuff A
WHERE IS_ENABLED = 'Y'
START WITH A.ID = '0001'
CONNECT BY PRIOR A.ID = A.PARENTID ;
-- 或者
SELECT '0001' AS ID
, UNAME AS NAME
FROM stuff A
WHERE IS_ENABLED = 'Y'
CONNECT BY PRIOR A.ID = A.PARENTID
START WITH A.ID = '0001';
返回的结果集从逻辑上可以被看作多颗树，使用 start with 对每棵树（也可能只有一颗）的根节点进行条件筛选，并根据connect by选择对应的子节点
3.2 死循环（nocycle）
对于数据极有可能会出现子节点重新指向根节点，从而导致结果集出现死循环的情况，Oracle会做出异常反馈
ORA-01436: CONNECT BY loop in user data
这种情况下 Oracle 提供了一个解决方法，就是添加 nocycle 关键字，添加后便不会报错，但循环的两行只会显示其中的第一条。
同时为了使数据问题的排查工作更加方便，Oracle在层次查询结果中添加了三列伪列：
- connect_by_iscycle：如果本行同时也是其祖先或祖先的子结点，那么该字段为1，否则为0
- connect_by_isleaf：如果本行是根节点，那么该字段为0，否则是1
- level：节点所在的深度，根结点的深度为1
SELECT '0001' AS "ID"
, UNAME AS "NAME"
, CONNECT_BY_ISLEAF AS "IsLeaf"
, CONNECT_BY_ISCYCLE AS "IsCycle"
，LEVEL AS "Level"
FROM stuff A
WHERE IS_ENABLED = 'Y'
START WITH A.ID = '0001'
CONNECT BY PRIOR A.ID = A.PARENTID
ORDER BY "NAME", "isLeaf", "IsCycle"， "Level"
3.3 利用LEVEL伪列进行 行转列
-- 随机获取一百个随机数
SELECT DBMS_RANDOM.VALUE
FROM DUAL
CONNECT BY LEVEL <= 100;
-- 按照日期展开明细
WITH TB AS (
SELECT 'U101' USER_ID
, DATE'2016-11-02' START_DT
, DATE'2016-11-06' END_DT
FROM DUAL
UNION ALL
SELECT 'U102'
, DATE'2015-06-01'
, DATE'2015-06-03'
FROM DUAL
)
SELECT TB.USER_ID, TB.START_DT + LEVEL - 1
FROM TB
CONNECT BY TB.USER_ID = PRIOR TB.USER_ID
AND LEVEL <= (TB.END_DT - TB.START_DT + 1)
-- 下面的语句是在欺骗Oracle取出不同的数据，因为通过 connect by 关联的两条数据相同是会报错的
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;
-- 转置字符串
SELECT SUBSTR('ABC', LEVEL, 1)
FROM DUAL
CONNECT BY LEVEL <= LENGTH('ABC');
3.4 分层查询特有运算符
在分层查询有两个特有的运算符，这两个运算符都是一元运算符：
- PRIOR: 用于修饰后面的字段代表为父节点的字段。
CONNECT BY PRIOR A.PARENTID = A.ID --代表子节点的PARENTID要等于父节点的ID
这句话同时也可以写成 CONNECT BY A.ID = PRIOR A.PARENTID [1] ,虽然输出的结果集是相同的，但是Oracle在查询数据时的逻辑不同，上面的语句是从根节点开始向子节点遍历，而[1]语句是从子节点开始向根节点遍历。
- CONNECT_BY_ROOT：被该运算符修饰的字段将表示根节点对应的列值。
CONNECT_BY_ROOT 运算符可在 SELECT 列表、WHERE 子句、GROUP BY 子句、HAVING 子句、ORDER BY 子句和 ORDER SIBLINGS BY 子句中使用，只要 SELECT 命令用于分层查询即可。同时 CONNECT_BY_ROOT 运算符不能在分层查询的 CONNECTBY 子句或 START WITH 子句中使用。可将 CONNECT_BY_ROOT 应用于一个涉及列的表达式，但这样做时，该表达式必须用圆括号括起。
3.5 排序
正常通过 ORDER BY 进行排序是对所有数据集进行排序，这样从结果上看会打乱原本树的结构，可以通过
ORDER SIBLINGS BY 对树中的每个结点按照层结构进行排序。也可以理解为是将结点与自己的兄弟节点进行排序。