SQL:如何限制在第一个找到的行上的联接? [英] SQL: how to limit a join on the first found row?
问题描述
如何在两个表之间建立联接,但仅限于满足联接条件的第一行?
How to make a join between two tables but limiting to the first row that meets the join condition ?
在这个简单的示例中,我想为table_A中的每一行获取table_B中满足条件的第一行:
In this simple example, I would like to get for every row in table_A the first row from table_B that satisfies the condition :
select table_A.id, table_A.name, table_B.city
from table_A join table_B
on table_A.id = table_B.id2
where ..
table_A (id, name)
1, John
2, Marc
table_B (id2, city)
1, New York
1, Toronto
2, Boston
The output would be:
1, John, New York
2, Marc, Boston
可能是Oracle提供了这样的功能(性能是一个问题).
May be Oracle provides such a function (performance is a concern).
推荐答案
此处的关键字是 FIRST .您可以使用解析函数FIRST_VALUE
或聚合构造FIRST
.
对于FIRST
或LAST
,性能永远不会比同等的FIRST_VALUE
或LAST_VALUE
构造差,并且经常会更好,因为我们没有多余的窗口排序,因此执行成本更低:
The key word here is FIRST. You can use analytic function FIRST_VALUE
or aggregate construct FIRST
.
For FIRST
or LAST
the performance is never worse and frequently better than the equivalent FIRST_VALUE
or LAST_VALUE
construct because we don't have a superfluous window sort and as a consequence a lower execution cost:
select table_A.id, table_A.name, firstFromB.city
from table_A
join (
select table_B.id2, max(table_B.city) keep (dense_rank first order by table_B.city) city
from table_b
group by table_B.id2
) firstFromB on firstFromB.id2 = table_A.id
where 1=1 /* some conditions here */
;
由于12c引入了运算符LATERAL
以及CROSS/OUTER APPLY
联接,因此可以在JOIN
子句的右侧使用相关子查询:
Since 12c introduced operator LATERAL
, as well as CROSS/OUTER APPLY
joins, make it possible to use a correlated subquery on right side of JOIN
clause:
select table_A.id, table_A.name, firstFromB.city
from table_A
cross apply (
select max(table_B.city) keep (dense_rank first order by table_B.city) city
from table_b
where table_B.id2 = table_A.id
) firstFromB
where 1=1 /* some conditions here */
;
这篇关于SQL:如何限制在第一个找到的行上的联接?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!