联接条件中的行数 [英] Rownum in the join condition
问题描述
最近我修复了一些错误:联接条件中存在rownum.
Recently I fixed the some bug: there was rownum in the join condition.
是这样的:在t1.id = t2.id和rownum< 2上左连接t1.因此，无论左连接"如何，它都应该只返回一行.
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".
进一步研究时，我意识到我不了解Oracle在左连接"条件下如何评估rownum. 我们创建两个样本表:主表和明细表.
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.
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.
然后我们有这个查询:
select * from master t
left join detail d on d.ref_master_id=t.id
结果集是可预测的:与该条件d.ref_master_id = t.id匹配，我们拥有主表中的所有行和明细表中的3行.
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.
然后我在连接条件中添加了"rownum = 1"，结果是相同的
Then I added "rownum=1" to the join condition and the result was the same
select * from master t
left join detail d on d.ref_master_id=t.id and rownum=1
最有趣的是，我设置"rownum< -666"并再次获得相同的结果！
The most interesting thing is that I set "rownum<-666" and got the same result again!
select * from master t
left join detail d on d.ref_master_id=t.id and rownum<-666.
由于结果集，我们可以说该条件在明细表中的3行中被评估为真".但是，如果我使用内部联接"，一切都会按预期进行.
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.
select * from master t
join detail d on d.ref_master_id=t.id and rownum<-666.
此查询不返回任何行，因为我无法想象rownum小于-666:-)
This query doesn’t return any row,because I can't imagine rownum to be less then -666 :-)
此外，如果我使用oracle语法进行外部联接，则使用(+)"也可以顺利进行.
Moreover, if I use oracle syntax for outer join, using "(+)" everything goes well too.
select * from master m ,detail t
where m.id=t.ref_master_id(+) and rownum<-666.
此查询也不会返回任何行.
This query doesn’t return any row too.
谁能告诉我，我对外部联接和rownum有什么误解?
Can anyone tell me, what I misunderstand with outer join and rownum?
推荐答案
ROWNUM是结果集的伪属性，而不是基表的伪属性. ROWNUM是在选择行之后但在按ORDER BY子句对它们进行排序之前定义的.
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写作中弄错了，所以这里是新信息:
edit: I was mistaken in my previous writeup of ROWNUM, so here's new information:
您可以在WHERE子句中以有限的方式使用ROWNUM，以测试它是否仅小于 个正整数.有关更多详细信息，请参见 ROWNUM伪列.
You can use ROWNUM in a limited way in the WHERE clause, for testing if it's less than a positive integer only. See ROWNUM Pseudocolumn for more details.
SELECT ... WHERE ROWNUM < 10
目前尚不清楚ROWNUM在JOIN子句的上下文中具有什么值，因此结果可能是不确定的.似乎使用ROWNUM对表达式进行了一些特殊情况的处理，例如WHERE ROWNUM > 10总是返回false.我不知道ROWNUM<-666在您的JOIN子句中如何工作，但是它没有意义，所以我不建议您使用它.
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 > 10 always returns false. I don't know how ROWNUM<-666 works in your JOIN clause, but it's not meaningful so I would not recommend using it.
无论如何，这无助于获取每个给定主行的第一个明细行.
In any case, this doesn't help you to fetch the first detail row for each given master row.
要解决此问题，您可以使用分析函数和PARTITION ，并将其与公用表表达式，这样您就可以在WHERE条件下访问行号列.
To solve this you can use analytic functions and PARTITION, and combine it with Common Table Expressions so you can access the row-number column in a further WHERE condition.
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;
这篇关于联接条件中的行数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
