有什么理由不只是创建另一个表表达式来覆盖更多字段并加入该表表达式中?
WITH DUPLICATEKEY(D1,D2,D3) AS
(
SELECT D1, D2, D3
FROM SOURCE
GROUP BY D1, D2, D3
HAVING COUNT(*)>1
)
WITH NODUPES(D1,D2,D3,C4,C5,C6) AS
(
SELECT
S.D1, S.D2, S.D3, S.C4, S.C5, S.C6
FROM SOURCE S
GROUP BY
S.D1, S.D2, S.D3, S.C4, S.C5, S.C6
HAVING COUNT(*)=1
)
SELECT S.D1, S.D2, S.D3, S.C4, S.C5, S.C6
FROM SOURCE S
INNER JOIN DUPLICATEKEY D
ON S.D1 = D.D1 AND S.D2 = D.D2 AND S.D3 = D.D3
INNER JOIN NODUPES D2
ON S.D1 = D2.D1 AND S.D2 = D2.D2 AND S.D3 = D2.D3
ORDER BY S.D1, S.D2, S.D3, S.C4, S.C5, S.C6