如何逐步执行和评估CONNECT BY
查询(以示例为例)。
假设我们有以下表格和一个connect by查询:
select * from mytable;
X
1
2
3
4
SELECT level, m.*
FROM mytable m
START with x = 1
CONNECT BY PRIOR x +1 = x OR PRIOR x + 2 = x
ORDER BY level;
步骤 1:
从表格mytable
中选择符合START WITH
条件的行,并将 LEVEL = 1 分配给返回的结果集:
CREATE TABLE step1 AS
SELECT 1 "LEVEL", X from mytable
WHERE x = 1;
SELECT * FROM step1;
LEVEL X
1 1
第二步
将等级增加1:
LEVEL = LEVEL + 1
使用 CONNECT BY
条件作为连接条件,将先前步骤返回的结果集与 mytable
表连接。
在此子句中,PRIOR column-name
是指先前步骤返回的结果集,简单的 column-name
则是指 mytable
表:
CREATE TABLE step2 AS
SELECT 2 "LEVEL", mytable.X from mytable
JOIN step1 "PRIOR"
ON "PRIOR".x +1 = mytable.x or "PRIOR".x + 2 = mytable.x;
select * from step2;
LEVEL X
2 2
2 3
第 x+1 步
重复 #2 直到最后一个操作返回空结果集。
第三步
CREATE TABLE step3 AS
SELECT 3 "LEVEL", mytable.X from mytable
JOIN step2 "PRIOR"
ON "PRIOR".x +1 = mytable.x or "PRIOR".x + 2 = mytable.x;
select * from step3;
LEVEL X
3 3
3 4
3 4
第四步
CREATE TABLE step4 AS
SELECT 4 "LEVEL", mytable.X from mytable
JOIN step3 "PRIOR"
ON "PRIOR".x +1 = mytable.x or "PRIOR".x + 2 = mytable.x;
select * from step4;
LEVEL X
4 4
第五步
CREATE TABLE step5 AS
SELECT 5 "LEVEL", mytable.X from mytable
JOIN step4 "PRIOR"
ON "PRIOR".x +1 = mytable.x or "PRIOR".x + 2 = mytable.x;
select * from step5;
no rows selected
第五步未返回任何行,因此现在我们完成查询
最后一步
UNION ALL
合并所有步骤的结果,并将其作为最终结果返回:
SELECT * FROM step1
UNION ALL
SELECT * FROM step2
UNION ALL
SELECT * FROM step3
UNION ALL
SELECT * FROM step4
UNION ALL
SELECT * FROM step5;
LEVEL X
1 1
2 2
2 3
3 3
3 4
3 4
4 4
现在让我们将上述步骤应用到您的查询中:
SELECT * FROM dual;
DUMMY
X
SELECT LEVEL FROM DUAL CONNECT BY rownum>5;
步骤 1
由于查询不包含 START WITH
子句,Oracle 从源表中选择所有记录:
CREATE TABLE step1 AS
SELECT 1 "LEVEL" FROM dual;
select * from step1;
LEVEL
1
第二步
CREATE TABLE step2 AS
SELECT 2 "LEVEL" from dual
JOIN step1 "PRIOR"
ON rownum > 5
select * from step2;
no rows selected
由于最后一步没有返回任何行,我们将完成查询。
最后一步
SELECT * FROM step1
UNION ALL
SELECT * FROM step2;
LEVEL
1
上一个查询的分析:
select level from dual connect by rownum<10;
我把这作为一项家庭作业留给你。
CONNECT BY
查询中如何分配ROWNUM
时是错误的。在CONNECT BY
查询中,结果集是唯一的,并且ROWNUM
从LEVEL = 1
连续分配到更高的级别;它不会在每个级别重新从1开始。有关详细信息,请参见稍后的线程,其中有人实际尝试完成此答案末尾留下的所谓“作业任务”。https://stackoverflow.com/questions/52899897/oracle-connect-by-rownum/52900132#52900132 - user5683823