在连接条件中使用Rownum

Question

在连接条件中使用Rownum

oraclerownum

5

最近我修复了一些错误：在联接条件中有rownum。

像这样：left join t1 on t1.id=t2.id and rownum<2。因此，它应该只返回一行，而不管“left join”。

当我进一步研究时，我意识到我不理解Oracle如何在“left join”条件中评估rownum。

让我们创建两个示例表：主表和详细表。

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

结果集是可预测的：我们有来自主表的所有行和3行来自细节表，这些行满足条件d.ref_master_id=t.id。

结果集

然后我在连接条件中添加了“rownum=1”，结果仍然相同。

select * from master t
left join detail d on d.ref_master_id=t.id and rownum=1

最有趣的是，我设置了“rownum<-666”，结果再次得到了相同的结果！

select * from master t
left join detail d on d.ref_master_id=t.id and rownum<-666.

基于结果集，我们可以说在详细表中有3行被判断为“真”。但是如果使用“inner join”，一切都会按照预期进行。

select * from master t
join detail d on d.ref_master_id=t.id and rownum<-666.

这个查询没有返回任何行，因为我无法想象rownum会小于-666 :-)

此外，如果我使用外连接的Oracle语法，使用“(+)”一切都很好。

select * from master m ,detail t
 where m.id=t.ref_master_id(+) and rownum<-666.

这个查询也没有返回任何行。

有人能告诉我，我在外连接和rownum方面误解了什么吗？

- Maxim Shevtsov

3个回答

2

如果您想从联接条件中获取前三个值，请将选择语句更改为以下内容。

    select * 
    from (select * 
          from master t left join detail d on d.ref_master_id=t.id)
    where rownum<3;

你将会得到所需的输出。当使用*时，请注意确切定义列名。

让我给出一个绝对的答案，你可以直接运行而不需要对代码进行任何更改。

    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;

- sasidhar

1

在连接中使用ROWNUM过滤器没有任何意义，但它不会被拒绝为无效。

解释计划将包括ROWNUM过滤器或排除它。如果它包括它，它将在应用其他连接条件之后将过滤器应用于详细表。因此，如果您输入ROWNUM = 100（永远不会满足），则所有详细行都将被排除，然后外部连接开始。

如果您输入ROWNUM = 1，则似乎会删除该过滤器。

如果您查询

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);

你得到了一些非常奇怪的东西。

它很可能被拒绝作为错误，所以要预料到发生一些毫无意义的事情。

- Gary Myers

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Bill Karwin · Accepted Answer

ROWNUM是结果集的伪属性，而不是基本表的属性。 ROWNUM在选择行之后定义，但在通过ORDER BY子句进行排序之前定义。

编辑：我之前对ROWNUM的描述有误，这里提供新的信息：

您可以在WHERE子句中有限地使用ROWNUM，仅测试它是否小于正整数。有关更多详细信息，请参见ROWNUM Pseudocolumn。

SELECT ... WHERE ROWNUM < 10

在JOIN子句的上下文中，ROWNUM的值不清楚，因此结果可能未定义。似乎对带有ROWNUM表达式进行了一些特殊处理，例如WHERE ROWNUM>10始终返回false。我不知道ROWNUM<-666在JOIN子句中的作用，但它没有意义，因此我不建议使用它。

无论如何，这并不能帮助您获取每个给定主行的第一行详细信息。

要解决此问题，您可以使用分析函数和PARTITION，并与通用表达式结合使用，以便在进一步的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;