Oracle分层查询
Oracle中START / WITH / CONNECT BY提供分层查询的能力，从START WITH开始遍历记录，递归查询结果集直到拿到所有满足条件的结果。
例如下面测试数据：
代码语言：javascript复制drop table sr_menu;
create table sr_menu(
id number(10) not null,
parent number(10),
title varchar2(50)
);
insert into sr_menu values (1, null, 'level 0');
insert into sr_menu values (2, 1, 'level 1');
insert into sr_menu values (3, 1, 'level 1');
insert into sr_menu values (4, 1, 'level 1');
insert into sr_menu values (5, 3, 'level 2');
commit;
select * from sr_menu;
ID PARENT TITLE
---------- ---------- --------------------------------------------------
1 level 0
2 1 level 1
3 1 level 1
5 3 level 2
4 1 level 1 有这样的逻辑关系：
代码语言：javascript复制level 0: 1
/ /
level 1: 2 3 4 5
/
level 2: 5业务上如果需要查询跟节点1的所有子节点，在Oracle中可以使用以下语法：
代码语言：javascript复制select * from sr_menu
start with id = 1
connect by prior id = parent;
ID PARENT TITLE
---------- ---------- --------------------------------------------------
1 level 0
2 1 level 1
3 1 level 1
5 3 level 2
4 1 level 1 查询时会用上一层的id=1（prior修饰）和当前的parent比较，查询出第二层符合条件的数据：
代码语言：javascript复制 2 1 level 1
3 1 level 1
4 1 level 1 后面继续递归，使用上一层的id=2/3/4去匹配下面的数据，从id=3中得到：
代码语言：javascript复制 5 3 level 2 Postgresql分层查询改造
Oracle 分层查询其实是一种递归查询的方式，用第一层查询的结果递归出后一层。在 Postgresql 中可以使用 WITH RECURSIVE 语法实现相同的功能。
普通的 WITH 子句可以实现 CTE 的功能，加上 RECURSIVE 关键字可以进一步在 WITH 内引用自己的输出实现递归，例如对于上面 SQL 的改写，可以实现完全相同的业务逻辑：
代码语言：javascript复制WITH RECURSIVE a AS (
SELECT id, parent, title
FROM sr_menu
WHERE id = 1
UNION ALL
SELECT d.id, d.parent, d.title
FROM sr_menu d
JOIN a ON a.id = d.parent )
SELECT id, parent, title FROM a;
id | parent | title
---- -------- ---------
1 | | level 0
2 | 1 | level 1
3 | 1 | level 1
4 | 1 | level 1
5 | 3 | level 2WITH 内使用 UNION ALL 的第一张对应 START WITH语句，一般是一个固定结果集的查询条件。
UNION ALL的第二张表join ... a.id，表示连接当前 with 子句的查询结果，这样反复递归直到所有数据查询完毕。
从递归深度也可以看出执行过程：
代码语言：javascript复制WITH RECURSIVE a AS (
SELECT id, parent, title, 1::integer recursion_level
FROM sr_menu
WHERE id = 1
UNION ALL
SELECT d.id, d.parent, d.title, a.recursion_level 1
FROM sr_menu d
JOIN a ON a.id = d.parent )
SELECT * FROM a;
id | parent | title | recursion_level
---- -------- --------- -----------------
1 | | level 0 | 1
2 | 1 | level 1 | 2
3 | 1 | level 1 | 2
4 | 1 | level 1 | 2
5 | 3 | level 2 | 3有关WITH RECURSIVE
代码语言：javascript复制WITH RECURSIVE t(n) AS (
VALUES (1)
UNION ALL
SELECT n 1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t;递归WITH的执行流程：
- 计算非递归项
（UNION ALL内的固定查询部分，例如上面的VALUES(1)）把结果放在临时表A中 - 临时表不为空，重复下列步骤：
- 计算递归项
（UNION ALL 内的递归部分），用临时表A当作递归自引用表。查询结果记录到临时表B - 用B的数据库覆盖A，清空B。
- 计算递归项
参考资料
https://www.postgresql.org/docs/11/queries-with.html#QUERIES-WITH-SELECT
