Oracle With 递归语句改写层级查询

Oracle With 语句结合UNION ALL 可以达到递归效果，功能与 START WITH CONNECT BY（层次查询）一致。

START WITH CONNECT BY 可按逻辑改写 替换 With 用法。

数据如下

drop table tab1;
create table tab1 (c1 int, c2 int);
insert into tab1 values(0,1);
insert into tab1 values(1,2);
insert into tab1 values(2,3);
insert into tab1 values(3,4);
insert into tab1 values(3,5);

Oracle 通过 With 语句实现递归查询，示例语句如下

WITH 
tmp AS (SELECT c1,c2,rownum rn FROM tab1),
tmp2(cc1,cc2,cc3,cc4) AS (
	SELECT c1,c2,0,CAST(rn AS varchar2(20)) FROM tmp WHERE tmp.c1=0
	UNION ALL 
	SELECT a.c1,a.c2,b.cc3 +1,b.cc4||a.rn FROM tmp a,tmp2 b WHERE b.cc2 = a.c1
)
SELECT * FROM tmp2;

结果如下：

cc1	cc2	cc3	cc4
0	1	0	1
1	2	1	12
2	3	2	123
3	4	3	1234
3	5	3	1235

WITH 语句递归语法 SQL1 UNION ALL SQL2

• SQL1 ：递归查询开始的树节点位置，示例中为 c1=0 的那一行为树顶位置
• SQL2 ：定义递归条件。示例中 where 条件 b.cc2 = a.c1 为递归条件， 对 tmp2 表进行递归结果集。每次基于 列cc2 的值在 基表 tmp 中查找符合要求的行。
• 投影列 cc3 +1 : 递归计算，类似 层级查询中的 LEVEL伪列
• 投影列 cc4||a.rn ：递归路径，类似层级查询中 SYS_CONNECT_BY_PATH（）函数

层次查询对应概念为

• SQL1 -> START WITH 开始条件
• SQL2中的递归条件 -> CONNECT BY 递归条件
  • 如例所示，递归表 tmp2 where 条件 b.cc2 = a.c1中， b.cc2 列 对应层级查询中 prior 列
• 非递归的相关查询条件 -> WHERE 子句条件
• 投影列 cc3 +1 -> level
• 投影列 cc4||a.rn -> SYS_CONNECT_BY_PATH（）函数

根据示例 改写 SQL 如下

select c1,c2,level-1,SYS_CONNECT_BY_PATH(rn,'')  from 
(select c1,c2,rownum rn from tab1 ) 
start with c1=0 connect by c1 = prior c2;