时间戳

首页 > 解决方案 > 根据条件删除重复值

tsql - 根据条件删除重复值

问题描述

我有两个表 IAM 和 IAM_audit 如下。我正在尝试获取更改国家或税收国家的帐号的结果。这很好用。但是，我希望结果只显示唯一的帐号，即使它们在税收国家和国家/地区都发生了变化。我不希望本例中的帐号 120 显示两次？:) 有没有办法。

初始表创建：

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')
        )

目前的结果

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

预期成绩：

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

标签： tsql

解决方案

只要audit_field在 select 子句中，您就会有重复项。重构原始查询后，您可以看到它是唯一可能导致重复的字段，如果您的#IAM表具有唯一的帐号，即 accnum. 这是因为 select 子句中的所有其他列都可以来自该#IAM表。

select distinct
      IAM.accnum
    , IAM.invnumber
    , IAM.taxcountry
    , IAMA.audit_field -- causing the 'duplicates' you describe
    , IAM.country
from
    #iam_audit1 IAMA
join
    #IAM IAM
    on 
        IAM.Accnum = IAMA.Accnum 
        and
        IAM.invnumber = IAMA.invnumber
where
    IAMA.Audit_Field IN ('TaxCountry', 'Country')
    and (
            (isnull(IAMA.Field_Before,'CAN') <> 'CAN' and isnull(IAMA.Field_After,'CAN') = 'CAN')
            or
            (isnull(IAMA.Field_Before,'CAN') = 'CAN' and isnull(IAMA.Field_After,'CAN') <> 'CAN')
        )

根据您的特定使用要求（和 SQL Server 版本），如果您确实不希望单个帐号有多个记录，则可以尝试几种不同的解决方法。

首先，您可以audit_field从 select 子句中删除。

select distinct
      IAM.accnum
    , IAM.invnumber
    , IAM.taxcountry
    , IAM.country
from
    #iam_audit1 IAMA
join
    #IAM IAM
    on 
        IAM.Accnum = IAMA.Accnum 
        and
        IAM.invnumber = IAMA.invnumber
where
    IAMA.Audit_Field IN ('TaxCountry', 'Country')
    and (
            (isnull(IAMA.Field_Before,'CAN') <> 'CAN' and isnull(IAMA.Field_After,'CAN') = 'CAN')
            or
            (isnull(IAMA.Field_Before,'CAN') = 'CAN' and isnull(IAMA.Field_After,'CAN') <> 'CAN')
        )

产生

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

其次，您可以获得帐号和更改的不同 audit_fields 的数量。

select distinct
      IAM.accnum
    , IAM.invnumber
    , IAM.taxcountry
    , count(distinct IAMA.audit_field) as cnt_audits_with_changes
    , IAM.country
from
    #iam_audit1 IAMA
join
    #IAM IAM
    on 
        IAM.Accnum = IAMA.Accnum 
        and
        IAM.invnumber = IAMA.invnumber
where
    IAMA.Audit_Field IN ('TaxCountry', 'Country')
    and (
            (isnull(IAMA.Field_Before,'CAN') <> 'CAN' and isnull(IAMA.Field_After,'CAN') = 'CAN')
            or
            (isnull(IAMA.Field_Before,'CAN') = 'CAN' and isnull(IAMA.Field_After,'CAN') <> 'CAN')
        )
group by
      IAM.accnum
    , IAM.invnumber
    , IAM.taxcountry
    , IAM.country

产生

accnum  invnumber   taxcountry  cnt_audits_with_changes country
120     131         CAN         2                       CAN
122     133         CAN         1                       CAN

第三，如果您使用的是 SQL Server 2017 或更高版本，您可以使用字符串聚合函数来连接audit_fields

select distinct
      IAM.accnum
    , IAM.invnumber
    , IAM.taxcountry
    , string_agg(IAMA.audit_field, ', ') as list_audits_with_changes
    , IAM.country
from
    #iam_audit1 IAMA
join
    #IAM IAM
    on 
        IAM.Accnum = IAMA.Accnum 
        and
        IAM.invnumber = IAMA.invnumber
where
    IAMA.Audit_Field IN ('TaxCountry', 'Country')
    and (
            (isnull(IAMA.Field_Before,'CAN') <> 'CAN' and isnull(IAMA.Field_After,'CAN') = 'CAN')
            or
            (isnull(IAMA.Field_Before,'CAN') = 'CAN' and isnull(IAMA.Field_After,'CAN') <> 'CAN')
        )
group by
      IAM.accnum
    , IAM.invnumber
    , IAM.taxcountry
    , IAM.country

产生

accnum  invnumber   taxcountry  list_audits_with_changes    country
120     131         CAN         country, taxcountry         CAN
122     133         CAN         taxcountry                  CAN

推荐阅读