Oracle SQL Tips
左连接的同时只输出关联表的一条记录
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 |
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
