与 Oracle CONNECT BY 的混淆
查询:
select level from dual connect by rownum<10;
给出从 1 到 9 的数字.
gives numbers from 1 to 9.
另一个查询:
SELECT LEVEL FROM DUAL CONNECT BY rownum>5;
输出:1
我在经理/员工等分层数据中使用了 CONNECT BY.但是我无法解释以上两个查询的结果.
I have used CONNECT BY in hierarchical data like manager/employee. But I fail to interpret the results of the above two queries.
我不想通过查询#2 实现任何特别的目的.我只想知道 oracle 如何解释查询.由于使用了CONNECT BY，是否有任何部分充当了父母和孩子?为什么结果是1?幕后发生了什么?
I am not trying to achieve anything special with query#2. I just want to know how oracle interprets the query. Does any part act as parent and child due to the use of CONNECT BY? Why is the result 1? What is happening behind the scenes?
推荐答案
如何执行和评估 CONNECT BY 查询 - 分步(通过示例).
How a CONNECT BY query is executed and evaluated - step by step (by example).
假设我们有下表和查询连接:
Say we have the following table and a connect by query:
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赋给返回的结果集:
Select rows from table mytable that meet a START WITH condition, assign LEVEL = 1 to the returned result set:
CREATE TABLE step1 AS
SELECT 1 "LEVEL", X from mytable
WHERE x = 1;
SELECT * FROM step1;
LEVEL X
---------- ----------
1 1
第 2 步
将级别增加 1:
LEVEL = LEVEL + 1
使用CONNECT BY条件作为连接条件，将上一步返回的结果集与mytable连接起来.
Join the result set returned in previous step with mytable using CONNECT BY conditions as the join conditions.
本子句中的PRIOR column-name指的是上一步返回的结果集，简单的column-name指的是mytable表:
In this clause PRIOR column-name refers to the resultset returned by previous step, and simple column-name refers to the mytable table:
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 直到最后一个操作返回一个空的结果集.
Repeat #2 until last operation returns an empty result set.
步骤 3
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
步骤 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
步骤 5
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
第 5 步没有返回任何行，所以现在我们完成了查询
Step 5 returned no rows, so now we finalize the query
最后一步
UNION ALL 将所有步骤的结果作为最终结果返回:
UNION ALL results of all steps and return it as the final result:
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
现在让我们将上述过程应用于您的查询:
Now let's apply the above procedure to your query:
SELECT * FROM dual;
DUMMY
-----
X
SELECT LEVEL FROM DUAL CONNECT BY rownum>5;
第一步
由于查询不包含START WITH子句，Oracle从源表中选择所有记录:
Since the query does not contain the START WITH clause, Oracle selects all records from the source table:
CREATE TABLE step1 AS
SELECT 1 "LEVEL" FROM dual;
select * from step1;
LEVEL
----------
1
第 2 步
CREATE TABLE step2 AS
SELECT 2 "LEVEL" from dual
JOIN step1 "PRIOR"
ON rownum > 5
select * from step2;
no rows selected
由于最后一步没有返回任何行，我们将完成我们的查询.
Since the last step returned no rows, we are going to finalize our query.
最后一步
SELECT * FROM step1
UNION ALL
SELECT * FROM step2;
LEVEL
----------
1
上次查询的分析:
select level from dual connect by rownum<10;
我把作业留给你.
相关文章