SQL：如何限制第一个找到的行的连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35255271/
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
SQL: how to limit a join on the first found row?
提问by kkung
How to make a join between two tables but limiting to the first row that meets the join condition ?
如何在两个表之间进行连接但仅限于满足连接条件的第一行？
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 :
在这个简单的例子中，我想为 table_A 中的每一行获取 table_B 中满足条件的第一行：
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
May be Oracle provides such a function (performance is a concern).
可能是 Oracle 提供了这样的功能（性能是一个问题）。
回答by 0xdb
The key word here is FIRST. You can use analytic function FIRST_VALUEor aggregate construct FIRST.
For FIRSTor LASTthe performance is never worse and frequently better than the equivalent FIRST_VALUEor LAST_VALUEconstruct because we don't have a superfluous window sort and as a consequence a lower execution cost:
这里的关键词是FIRST。您可以使用解析函数FIRST_VALUE或聚合构造FIRST。
因为FIRSTorLAST的性能永远不会比等效的FIRST_VALUEorLAST_VALUE构造更差并且经常更好，因为我们没有多余的窗口排序，因此执行成本更低：
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 */
;
Since 12c introduced operator LATERAL, as well as CROSS/OUTER APPLYjoins, make it possible to use a correlated subquery on right side of JOINclause:
由于 12c 引入了 operatorLATERAL以及CROSS/OUTER APPLY连接，因此可以在JOIN子句右侧使用相关子查询：
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 */
;
回答by Husqvik
If you want just single value a scalar subquery can be used:
如果您只需要单个值，则可以使用标量子查询：
SELECT
id, name, (SELECT city FROM table_B WHERE id2 = table_A.id AND ROWNUM = 1) city
FROM
table_A
回答by Mihai
select table_A.id, table_A.name,
FIRST_VALUE(table_B.city) IGNORE NULLS
OVER (PARTITION BY table_B.id2 ORDER BY table_B.city) AS "city"
from table_A join table_B
on table_A.id = table_B.id2
where ..
回答by MT0
Query:
查询：
SELECT a.id,
a.name,
b.city
FROM table_A a
INNER JOIN
( SELECT id2,
city
FROM (
SELECT id2,
city,
ROW_NUMBER() OVER ( PARTITION BY id2 ORDER BY NULL ) rn
FROM Table_B
)
WHERE rn = 1
) b
ON ( a.id = b.id2 )
--WHERE ...
Outputs:
输出：
ID NAME CITY
---------- ---- --------
1 John New York
2 Marc Boston
回答by Alessandro Rossi
On Oracle12c there finally is the new cross/outer apply operatorthat will allow what you asked for without any workaround.
在 Oracle12c 上，终于有了新的cross/outer apply 运算符，它可以让您在没有任何解决方法的情况下满足您的要求。
the following is an example that looks on dictionary views for just one of the (probably)many objects owned by those users having their name starting with 'SYS':
以下是一个示例，该示例仅查看名称以“SYS”开头的那些用户拥有的（可能）许多对象之一的字典视图：
select *
from (
select USERNAME
from ALL_USERS
where USERNAME like 'SYS%'
) U
cross apply (
select OBJECT_NAME
from ALL_OBJECTS O
where O.OWNER = U.USERNAME
and ROWNUM = 1
)
On Oracle 11g and prior versions you should only use workarounds that generally full scan the second table based on IDs of the second table to get the same results, but for testing puposes you may enable the lateral operator(also available on 12c without need of enabling new stuff) and use this other one
在 Oracle 11g 和以前的版本上，您应该只使用通常基于第二个表的 ID 对第二个表进行全面扫描以获得相同结果的变通方法，但为了测试目的，您可以启用横向运算符（在 12c 上也可用，无需启用新东西）并使用另一个
-- Enables some new features
alter session set events '22829 trace name context forever';
select *
from (
select USERNAME
from ALL_USERS
where USERNAME like 'SYS%'
) U,
lateral (
select OBJECT_NAME
from ALL_OBJECTS O
where O.OWNER = U.USERNAME
and ROWNUM = 1
);
回答by Martien de Jong
This solution uses the whole table, like in a regular join, but limits to the first row. I am posting this because for me the other solutions were not sufficient because they use one field only, or they have performance issues with large tables. I am no expert at Oracle so if someone can improve this please do so, I will be happy to use your version.
此解决方案使用整个表，就像在常规连接中一样，但仅限于第一行。我发布这个是因为对我来说其他解决方案是不够的，因为它们只使用一个字段，或者它们有大表的性能问题。我不是 Oracle 专家，所以如果有人可以改进这一点，请这样做，我很乐意使用您的版本。
select *
from tableA A
cross apply (
select *
from (
select B.*,
ROW_NUMBER() OVER (
-- replace this by your own partition/order statement
partition by B.ITEM_ID order by B.DELIVERYDATE desc
) as ROW_NUM
from tableB B
where
A.ITEM_ID=B.ITEM_ID
)
where ROW_NUM=1
) B
