oracle 连接条件中的 Rownum
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6603968/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rownum in the join condition
提问by Maxim Shevtsov
Recently I fixed the some bug: there was rownum in the join condition.
最近我修复了一些错误：连接条件中有rownum。
Something like this: left join t1 on t1.id=t2.id and rownum<2. So it was supposed to return only one row regardless of the “left join”.
像这样：在 t1.id=t2.id 和 rownum<2 上左连接 t1。因此，无论“左连接”如何，它都应该只返回一行。
When I looked further into this, I realized that I don't understand how Oracle evaluates rownum in the "left join" condition. Let's create two sampe tables: master and detail.
当我进一步研究这个时，我意识到我不明白 Oracle 如何在“左连接”条件下评估 rownum。让我们创建两个样本表：master 和 detail。
create table MASTER
(
ID NUMBER not null,
NAME VARCHAR2(100)
)
;
alter table MASTER
add constraint PK_MASTER primary key (ID);
prompt Creating DETAIL...
create table DETAIL
(
ID NUMBER not null,
REF_MASTER_ID NUMBER,
NAME VARCHAR2(100)
)
;
alter table DETAIL
add constraint PK_DETAIL primary key (ID);
alter table DETAIL
add constraint FK_DETAIL_MASTER foreign key (REF_MASTER_ID)
references MASTER (ID);
prompt Disabling foreign key constraints for DETAIL...
alter table DETAIL disable constraint FK_DETAIL_MASTER;
prompt Loading MASTER...
insert into MASTER (ID, NAME)
values (1, 'First');
insert into MASTER (ID, NAME)
values (2, 'Second');
commit;
prompt 2 records loaded
prompt Loading DETAIL...
insert into DETAIL (ID, REF_MASTER_ID, NAME)
values (1, 1, 'REF_FIRST1');
insert into DETAIL (ID, REF_MASTER_ID, NAME)
values (2, 1, 'REF_FIRST2');
insert into DETAIL (ID, REF_MASTER_ID, NAME)
values (3, 1, 'REF_FIRST3');
commit;
prompt 3 records loaded
prompt Enabling foreign key constraints for DETAIL...
alter table DETAIL enable constraint FK_DETAIL_MASTER;
set feedback on
set define on
prompt Done.
Then we have this query :
然后我们有这个查询：
select * from master t
left join detail d on d.ref_master_id=t.id
The result set is predictable: we have all the rows from the master table and 3 rows from the detail table that matched this condition d.ref_master_id=t.id.
结果集是可预测的：我们有来自主表的所有行和来自详细表的 3 行与此条件 d.ref_master_id=t.id 匹配。
Then I added “rownum=1” to the join condition and the result was the same
然后我在连接条件中添加了“rownum=1”，结果是一样的
select * from master t
left join detail d on d.ref_master_id=t.id and rownum=1
The most interesting thing is that I set “rownum<-666” and got the same result again!
最有趣的是，我设置了“rownum<-666”，又得到了同样的结果！
select * from master t
left join detail d on d.ref_master_id=t.id and rownum<-666.
Due to the result set we can say that this condition was evaluated as “True” for 3 rows in the detail table. But if I use “inner join” everything goes as supposed to be.
由于结果集，我们可以说对于详细信息表中的 3 行，此条件被评估为“真”。但是，如果我使用“内部连接”，一切都会按预期进行。
select * from master t
join detail d on d.ref_master_id=t.id and rownum<-666.
This query doesn't return any row,because I can't imagine rownum to be less then -666 :-)
此查询不返回任何行，因为我无法想象 rownum 小于 -666 :-)
Moreover, if I use oracle syntax for outer join, using “(+)” everything goes well too.
此外，如果我使用 oracle 语法进行外连接，使用“(+)”也一切顺利。
select * from master m ,detail t
where m.id=t.ref_master_id(+) and rownum<-666.
This query doesn't return any row too.
此查询也不返回任何行。
Can anyone tell me, what I misunderstand with outer join and rownum?
谁能告诉我，我对外连接和 rownum 的误解是什么？
回答by Bill Karwin
ROWNUM is a pseudo-attribute of result sets, not of base tables. ROWNUM is defined after rows are selected, but before they're sorted by an ORDER BY clause.
ROWNUM 是结果集的伪属性，而不是基表的伪属性。ROWNUM 是在选择行之后定义的，但在它们由 ORDER BY 子句排序之前定义。
edit:I was mistaken in my previous writeup of ROWNUM, so here's new information:
编辑：我之前写的 ROWNUM 写错了，所以这里有新信息：
You can use ROWNUM in a limited way in the WHEREclause, for testing if it's less thana positive integer only. See ROWNUM Pseudocolumnfor more details.
您可以在WHERE子句中以有限的方式使用 ROWNUM，仅用于测试它是否小于正整数。有关更多详细信息，请参阅ROWNUM Pseudocolumn。
SELECT ... WHERE ROWNUM < 10
It's not clear what value ROWNUM has in the context of a JOIN clause, so the results may be undefined. There seems to be some special-case handling of expressions with ROWNUM, for instance WHERE ROWNUM > 10always returns false. I don't know how ROWNUM<-666works in your JOIN clause, but it's not meaningful so I would not recommend using it.
不清楚 ROWNUM 在 JOIN 子句的上下文中具有什么值，因此结果可能未定义。似乎有一些特殊情况处理 ROWNUM 的表达式，例如WHERE ROWNUM > 10总是返回 false。我不知道ROWNUM<-666你的 JOIN 子句是如何工作的，但它没有意义，所以我不建议使用它。
In any case, this doesn't help you to fetch the first detail row for each given master row.
在任何情况下，这都无助于获取每个给定主行的第一个详细信息行。
To solve this you can use analytic functions and PARTITION, and combine it with Common Table Expressionsso you can access the row-number column in a further WHEREcondition.
要解决此问题，您可以使用分析函数 andPARTITION，并将其与Common Table Expressions结合使用，以便您可以在进一步的WHERE条件下访问行号列。
WITH numbered_cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY d.something) AS rn
FROM master t LEFT OUTER JOIN detail d ON d.ref_master_id = t.id
)
SELECT *
FROM numbered_cte
WHERE rn = 1;
回答by sasidhar
if you want to get the first three values from the join condition change the select statement like this.
如果您想从连接条件中获取前三个值，请像这样更改选择语句。
select *
from (select *
from master t left join detail d on d.ref_master_id=t.id)
where rownum<3;
You will get the required output. Take care on unambigiously defined column names when using *
您将获得所需的输出。使用 * 时注意明确定义的列名
Let me give an absolute answer which u can run directly with out making any changes to the code.
让我给出一个绝对的答案，您可以直接运行而无需对代码进行任何更改。
select *
from (select t.id,t.name,d.id,d.ref_master_id,d.name
from master t left join detail d on d.ref_master_id=t.id)
where rownum<3;
回答by Gary Myers
A ROWNUM filter doesn't make any sense in a join, but it isn't being rejected as invalid.
ROWNUM 过滤器在连接中没有任何意义，但不会因为无效而被拒绝。
The explain plan will either include the ROWNUM filter or exclude it. If it includes it, it will apply the filter to the detail table after applying the other join condition(s). So if you put in ROWNUM=100 (which will never be satisfied) all the detail rows are excluded and then the outer join kicks in.
解释计划将包括 ROWNUM 过滤器或排除它。如果包含它，它将在应用其他连接条件后将过滤器应用到明细表。因此，如果您输入 ROWNUM=100（永远不会满足），则所有详细信息行都将被排除，然后外连接开始。
If you put in ROWNUM=1 it seems to drop the filter.
如果您输入 ROWNUM=1，它似乎会删除过滤器。
And if you query
如果你查询
with
a as (select rownum a_val from dual connect by level < 10),
b as (select rownum*2 b_val from dual connect by level < 10)
select * from a left join b on a_val < b_val and rownum in (1,3);
you get something totally weird.
你会得到一些非常奇怪的东西。
It probably should be rejected as an error, so expect nonsensical things to happen
它可能应该作为错误被拒绝，所以期待无意义的事情发生
