使用 oracle 在外连接中只获取一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/658259/
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
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-18 17:57:14 来源:igfitidea点击:
Getting only one row in outer join using oracle
提问by Peter Lang
I've got a table and want to outer-join another table, getting only the first row (the one with lowest nr) of the second table using Oracle 10g.
我有一个表，想要外部连接另一个表，使用 Oracle 10g 只获取第二个表的第一行（nr 最低的行）。
Edit:nr is unique within an id
编辑：nr 在 id 中是唯一的
Table x Table y
id id nr code
1 1 1 B
2 1 2 A
3 2 2 A
Expected result:
id nr code
1 1 B
2 2 A
3 NULL NULL
Example with test data (does not do the limitation to single row but should allow faster testing):
测试数据示例（不限制单行，但应该允许更快的测试）：
WITH
x AS( SELECT 1 id FROM dual UNION SELECT 2 FROM dual UNION SELECT 3 FROM dual ),
y AS( SELECT 1 id, 1 nr, 'B' code FROM dual
UNION SELECT 1, 2, 'A' FROM dual
UNION SELECT 2, 2, 'A' FROM dual
) -- end of test data
SELECT x.id, y.nr, y.code
FROM x
LEFT OUTER JOIN y ON ( y.id = x.id )
回答by Quassnoi
WITH
x AS( SELECT 1 id FROM dual UNION SELECT 2 FROM dual UNION SELECT 3 FROM dual ),
y AS( SELECT 1 id, 1 nr, 'B' code FROM dual
UNION SELECT 1, 2, 'A' FROM dual
UNION SELECT 2, 2, 'A' FROM dual
) -- end of test data
SELECT *
FROM (
SELECT x.id, y.nr, y.code, ROW_NUMBER() OVER (PARTITION BY x.id ORDER BY y.nr) AS rn
FROM x
LEFT OUTER JOIN y
ON y.id = x.id
)
WHERE rn = 1
回答by Peter Lang
WITH
x AS( SELECT 1 id FROM dual UNION SELECT 2 FROM dual UNION SELECT 3 FROM dual ),
y AS( SELECT 1 id, 1 nr, 'B' code FROM dual
UNION SELECT 1, 2, 'A' FROM dual
UNION SELECT 2, 2, 'A' FROM dual
) -- end of test data
SELECT x.id, y.nr, y.code
FROM x
LEFT OUTER JOIN y ON ( y.id = x.id )
WHERE rownum = 1 --Add this
