与Oracle CONNECT BY的混淆 [英] Confusion with Oracle CONNECT BY
问题描述
查询:
select level from dual connect by rownum<10;
给出从1到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.
修改: 我没有尝试通过query#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
STEP 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;
第1步
由于查询不包含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;
我留给你做家庭作业.
这篇关于与Oracle CONNECT BY的混淆的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
