如何在UNION查询中筛选CardCode与DocTotal的重复数据

需求说明 #

现有SQL已通过UNION ALL合并了状态为'O'的发票表[OINV]和贷项通知单表[ORIN]的开放数据，需要从中筛选出CardCode与DocTotal字段同时重复的记录。

示例输入 #

CardCode    CardName        DocTotal
12          Name12              10
12          Name12              20
13          Name13              10
14          Name14              15
14          Name14              15
15          Name15              10
16          Name16              15
17          Name17              20
17          Name17              15
18          Name18              14
19          Name19              33
19          Name19              33
19          Name19              33

期望输出 #

14          Name14              15
14          Name14              15
19          Name19              33
19          Name19              33
19          Name19              33

当前查询代码 #

SELECT 
CardCode, 
CardName, 
DocTotal

FROM 
(
SELECT 
T0.[CardCode], 
T0.[CardName],
T0.[DocNum],  
T0.[DocTotal] 

FROM 
ORIN T0 

WHERE 
T0.[DocStatus] = 'O'

UNION ALL

SELECT 
T0.[CardCode], 
T0.[CardName],
T0.[DocNum],  
T0.[DocTotal] 

FROM 
OINV T0 

WHERE 
T0.[DocStatus] = 'O'
) as T1

修改后的查询方案 #

方案一：使用窗口函数（写法简洁）

通过窗口函数COUNT()按CardCode和DocTotal分组统计出现次数，直接筛选次数大于1的记录：

SELECT 
CardCode, 
CardName, 
DocTotal
FROM 
(
SELECT 
T0.[CardCode], 
T0.[CardName],
T0.[DocNum],  
T0.[DocTotal],
COUNT(*) OVER(PARTITION BY T0.[CardCode], T0.[DocTotal]) AS RecordCount
FROM 
ORIN T0 
WHERE 
T0.[DocStatus] = 'O'

UNION ALL

SELECT 
T0.[CardCode], 
T0.[CardName],
T0.[DocNum],  
T0.[DocTotal],
COUNT(*) OVER(PARTITION BY T0.[CardCode], T0.[DocTotal]) AS RecordCount
FROM 
OINV T0 
WHERE 
T0.[DocStatus] = 'O'
) as T1
WHERE T1.RecordCount > 1

方案二：先统计重复组合再关联（大数据量更高效）

先通过GROUP BY+HAVING找出重复的CardCode+DocTotal组合，再关联原数据集筛选对应记录：

SELECT 
T1.CardCode, 
T1.CardName, 
T1.DocTotal
FROM 
(
SELECT 
T0.[CardCode], 
T0.[CardName],
T0.[DocNum],  
T0.[DocTotal] 
FROM 
ORIN T0 
WHERE 
T0.[DocStatus] = 'O'

UNION ALL

SELECT 
T0.[CardCode], 
T0.[CardName],
T0.[DocNum],  
T0.[DocTotal] 
FROM 
OINV T0 
WHERE 
T0.[DocStatus] = 'O'
) as T1
INNER JOIN (
    SELECT CardCode, DocTotal
    FROM (
        SELECT CardCode, DocTotal FROM ORIN WHERE DocStatus = 'O'
        UNION ALL
        SELECT CardCode, DocTotal FROM OINV WHERE DocStatus = 'O'
    ) AS T2
    GROUP BY CardCode, DocTotal
    HAVING COUNT(*) > 1
) AS T3 ON T1.CardCode = T3.CardCode AND T1.DocTotal = T3.DocTotal

方案说明 #

• 方案一无需额外关联，代码简洁易读，适合大多数常规数据量场景。
• 方案二先缩小重复组合范围，再关联筛选，在数据量较大时能减少后续处理的数据量，性能更优。

内容的提问来源于stack exchange，提问作者WJ496582