按条件去重：如何使多字段变更的账号在SQL查询结果中仅显示一次

看起来你已经把筛选country/taxcountry变更记录的逻辑跑通了，现在就差解决同一accnum+invnumber组合重复出现的问题对吧？原查询里的DISTINCT之所以没用，是因为audit_field列的值不同（一会是country一会是taxcountry），所以这两条记录会被判定为不同。下面给你两种靠谱的解决方案：

先回顾下你的场景信息 #

表结构与测试数据 #

create table #Iam_audit1 ( 
    accnum int, 
    invnumber int, 
    audit_field varchar(10), 
    field_before varchar(10), 
    field_after varchar(10), 
    modified_date datetime 
) 
insert into #Iam_audit1 (accnum, invnumber, audit_field, field_before,field_after, modified_date) 
values 
(120, 131, 'country', 'US', 'CAN','2014-08-09'), 
(120, 131, 'taxcountry', 'US','CAN', '2015-07-09'), 
(121, 132, 'country', 'CAN','US', '2014-09-15'), 
(121, 132, 'taxcountry', 'CAN', 'US','2015-09-14'), 
(122, 133, 'Taxcountry','CAN','US','2014-05-27') 

create table #Iam ( 
    Accnum int, 
    invnumber int, 
    country varchar(10) , 
    Taxcountry varchar(10) 
) 
insert into #Iam (Accnum, invnumber, country, taxcountry) 
values 
(120, 131, 'CAN', 'CAN'), 
(120, 132, 'US', 'US'), 
(122, 133, 'CAN', 'CAN')

当前查询语句（存在重复问题） #

Select distinct 
    IAMA.accnum, 
    IAMA.invnumber, 
    IAM.taxcountry, 
    Iama.audit_field, 
    IAM.country 
From #iam_audit1 IAMA 
join #IAM iam on iam.Accnum = iama.Accnum AND iam.invnumber = iama.invnumber 
Where Audit_Field IN ('TaxCountry', 'Country') 
AND ( 
    (isnull(Field_Before,'CAN') <> 'CAN' AND isnull(Field_After,'CAN') = 'CAN') 
    OR (isnull(Field_Before,'CAN') = 'CAN' AND isnull(Field_After,'CAN') <> 'CAN') 
)

方案一：用窗口函数精准控制取哪条记录 #

推荐用ROW_NUMBER()窗口函数，给每个accnum+invnumber分组的记录排名，然后只保留每组的第一条。你可以自己决定取最早变更的还是最新变更的记录：

WITH RankedAudits AS (
    SELECT 
        IAMA.accnum, 
        IAMA.invnumber, 
        IAM.taxcountry, 
        IAMA.audit_field, 
        IAM.country,
        -- 按账号+发票号分组，给每条记录排号
        ROW_NUMBER() OVER (
            PARTITION BY IAMA.accnum, IAMA.invnumber 
            ORDER BY IAMA.modified_date ASC -- 取最早变更的记录，换成DESC就是取最新的
        ) AS record_rank
    FROM #iam_audit1 IAMA 
    JOIN #IAM iam ON iam.Accnum = iama.Accnum AND iam.invnumber = iama.invnumber 
    WHERE Audit_Field IN ('TaxCountry', 'Country') 
    AND ( 
        (ISNULL(Field_Before,'CAN') <> 'CAN' AND ISNULL(Field_After,'CAN') = 'CAN') 
        OR (ISNULL(Field_Before,'CAN') = 'CAN' AND ISNULL(Field_After,'CAN') <> 'CAN') 
    )
)
SELECT 
    accnum, 
    invnumber, 
    taxcountry, 
    audit_field, 
    country
FROM RankedAudits
WHERE record_rank = 1; -- 只留每组的第一条

为什么这么做？ #

• PARTITION BY把相同账号和发票号的记录归为一组
• ORDER BY决定组内的排序规则，比如你想保留最早变更的country记录，就按modified_date ASC排序；如果想留最新的taxcountry记录，换成DESC就行
• 最后筛选record_rank=1，确保每个组合只出现一次

方案二：用GROUP BY快速去重（适合不关心保留哪个audit_field的场景） #

如果对于保留country还是taxcountry的记录没有要求，只是想随便留一条，用GROUP BY更简单：

SELECT 
    IAMA.accnum, 
    IAMA.invnumber, 
    MAX(IAM.taxcountry) AS taxcountry, -- 同一组内这些值都一样，MAX/MIN随便用
    MIN(IAMA.audit_field) AS audit_field, -- 取字段名排序靠前的，比如'country'会比'taxcountry'先被选中
    MAX(IAM.country) AS country
FROM #iam_audit1 IAMA 
JOIN #IAM iam ON iam.Accnum = iama.Accnum AND iam.invnumber = iama.invnumber 
WHERE Audit_Field IN ('TaxCountry', 'Country') 
AND ( 
    (ISNULL(Field_Before,'CAN') <> 'CAN' AND ISNULL(Field_After,'CAN') = 'CAN') 
    OR (ISNULL(Field_Before,'CAN') = 'CAN' AND ISNULL(Field_After,'CAN') <> 'CAN') 
)
GROUP BY IAMA.accnum, IAMA.invnumber;

这个写法正好能匹配你的期望结果，因为MIN(audit_field)会优先取到country，和你想要的输出一致。

最终验证结果 #

两种方案跑出来的结果都会是你想要的：

accnum invnumber taxcountry audit_field country
120    131       CAN        country     CAN
122    133       CAN        Taxcountry  CAN

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