Oracle，按行连接 [英] Oracle, Connect By rownum
问题描述
我试图找到一些有关通过引擎"进行连接的信息. 我发现了这篇文章:与Oracle CONNECT BY的混淆
用户krokodilko回答说:
The analyze of the last query:
select level from dual connect by rownum<10;
I leave to you as a homework assignment.
所以我尝试完全按照查询的要求进行操作
Select rownum from dual connect by rownum < 3
这是我的作品":
CREATE TABLE step1 AS
SELECT 1 "LEVEL" FROM dual;
SELECT * FROM step1;
create table step2 as
SELECT 2 "LEVEL" from dual
JOIN step1 "PRIOR" on rownum <=3;
SELECT * FROM step2;
create table step3 as
select 3 "LEVEL" from dual
join step2 "PRIOR" on rownum <=3;
SELECT * FROM step3;
create table step4 as
select 4 "LEVEL" from dual
join step3 "PRIOR" on rownum <=3;
SELECT * FROM step4;
但是最后一个SELECT仍然返回行.我误会了吗?每当我选择LEVEL + 1"LEVEL"时，它的rownum = 1，因此始终为true.那么我的步骤失败了吗?
Krokodilko的答案中的解释是错误的.您可能会忽略正确答案"标记和无数的反对，但这仍然是错误的.有趣的是，他作为练习完全离开了事实，证明了解释是错误的.
CONNECT BY查询无法正常工作，就像在每个步骤中都生成新表一样(或SELECT语句的新输出行集).这是论点中的错误.
相反，总体上(跨所有步骤)仅生成一个行集.的确，新行是根据上一步生成的行添加的；但是行集本身是一个，并且正在增长，而不是单独的行集.
这与ROWNUM特别相关. ROWNUM被分配给一个从1开始的单个结果"行集中的行.在CONNECT BY查询中，只有一个行集，并且ROWNUM从1到n递增.
如果Krokodilko的答案正确，则ROWNUM将在每一步以1重新开始.显然不是这种情况:让我们在标准"分层查询中进行尝试.
select empno, ename, mgr, level, rownum
from scott.emp
start with mgr is null
connect by prior empno = mgr
;
EMPNO ENAME MGR LEVEL ROWNUM
---------- ---------- ---------- ---------- ----------
7839 KING 1 1
7566 JONES 7839 2 2
7788 SCOTT 7566 3 3
7876 ADAMS 7788 4 4
7902 FORD 7566 3 5
7369 SMITH 7902 4 6
7698 BLAKE 7839 2 7
7499 ALLEN 7698 3 8
7521 WARD 7698 3 9
7654 MARTIN 7698 3 10
7844 TURNER 7698 3 11
7900 JAMES 7698 3 12
7782 CLARK 7839 2 13
7934 MILLER 7782 3 14
I tried to find some informations about connect by "engine". I found this post: Confusion with Oracle CONNECT BY
User krokodilko answered and says:
The analyze of the last query:
select level from dual connect by rownum<10;
I leave to you as a homework assignment.
So i tried to do exactly as described to query
Select rownum from dual connect by rownum < 3
And here's my "work":
CREATE TABLE step1 AS
SELECT 1 "LEVEL" FROM dual;
SELECT * FROM step1;
create table step2 as
SELECT 2 "LEVEL" from dual
JOIN step1 "PRIOR" on rownum <=3;
SELECT * FROM step2;
create table step3 as
select 3 "LEVEL" from dual
join step2 "PRIOR" on rownum <=3;
SELECT * FROM step3;
create table step4 as
select 4 "LEVEL" from dual
join step3 "PRIOR" on rownum <=3;
SELECT * FROM step4;
But last SELECT still returns rows. Am I misunderstood something? Every time i Select LEVEL + 1 "LEVEL" it has rownum = 1 so it's always true. So am i failed steps?
The explanation in Krokodilko's answer is simply wrong. You may disregard the "Correct Answer" mark and the numerous upvotes, it's still wrong. It is interesting that he left as an exercise exactly the case that proves the explanation is wrong.
A CONNECT BY query doesn't work "as if" new tables (or new output rowsets of SELECT statements, anyway) are generated at each step. This is the mistake in the argument.
Rather, there is only one rowset generated overall (across all steps). It is true that new rows are added based on the rows generated at the previous step; but the rowset itself is one, and growing, not separate rowsets.
This is particularly relevant with regard to ROWNUM. ROWNUM is assigned to rows in a single "result" rowset, starting with 1. In a CONNECT BY query, there is only one rowset, and ROWNUM goes from 1 to n in an increasing sequence.
If Krokodilko's answer were correct, then ROWNUM would restart at 1 at each step. This is clearly not the case: let's try it on a "standard" hierarchical query.
select empno, ename, mgr, level, rownum
from scott.emp
start with mgr is null
connect by prior empno = mgr
;
EMPNO ENAME MGR LEVEL ROWNUM
---------- ---------- ---------- ---------- ----------
7839 KING 1 1
7566 JONES 7839 2 2
7788 SCOTT 7566 3 3
7876 ADAMS 7788 4 4
7902 FORD 7566 3 5
7369 SMITH 7902 4 6
7698 BLAKE 7839 2 7
7499 ALLEN 7698 3 8
7521 WARD 7698 3 9
7654 MARTIN 7698 3 10
7844 TURNER 7698 3 11
7900 JAMES 7698 3 12
7782 CLARK 7839 2 13
7934 MILLER 7782 3 14
这篇关于Oracle，按行连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
