左连接的同时只输出关联表的一条记录
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
结果如下：
ID | NR | CODE | RN |
1 | 1 | B | 1 |
2 | 2 | A | 1 |
3 | 1 |