查询以查找返回多余结果集的重复项

我有一个查询旨在查找重复项,特别是在country inc和CompanyRegNum字段中,但gtid不应重复 . 当我运行SQL时,它返回数百万条记录 . 我知道有一个事实,即没有百万的CompanyRegNums,所以查询做错了什么,但对于我的生活,我不知道是什么 .

select  distinct
    @reportIdCompRegCompInc   ,
    wp1.CrmPartyId,
    wp1.GtId,  
    convert(varchar(20), 
            hashbytes('SHA1', isnull(wp1.CrmPartyId, '') + isnull(wp1.GtId, 
 '') ), 
            2), 
    (select AttributeId from REPORTING.Attribute where Name = 'GT ID'),                     
 @WeccoSysId,       wp1.GtId,
    (select AttributeId from REPORTING.Attribute where Name = 'CRM Party 
 Id'),              @WeccoSysId,        wp1.CrmPartyId,
    (select AttributeId from REPORTING.Attribute where Name = 'Legal Name'),                
@WeccoSysId,        wp1.LegalName,
    (select AttributeId from REPORTING.Attribute where Name = 'Business 
Class'),            @WeccoSysId,        wp1.BusinessClass,
    (select AttributeId from REPORTING.Attribute where Name = 'Relationship 
Manager Name'), @WeccoSysId,        wp1.RmFullName,     
    (select AttributeId from REPORTING.Attribute where Name = 'PbeName'),                   
@WeccoSysId,        wp1.PbeFullName,            
    (select AttributeId from REPORTING.Attribute where Name = 'Overall 
Status'),           @WeccoSysId,        wp1.OverallStatus,
    (select AttributeId from REPORTING.Attribute where Name = 'Country of 
Incorp'),           @WeccoSysId,        wp1.CountryInc,
    (select AttributeId from Reporting.Attribute where Name = 'Company 
 Reg'),               @WeccoSysId,        wp1.CompanyRegNum,
    (select AttributeId from REPORTING.Attribute where Name = 'GT ID'),                     
 @WeccoSysId,       wp2.GtId,
    (select AttributeId from REPORTING.Attribute where Name = 'CRM Party 
 Id'),              @WeccoSysId,        wp2.CrmPartyId,
    (select AttributeId from REPORTING.Attribute where Name = 'Legal Name'),                @WeccoSysId,        wp2.LegalName,
    (select AttributeId from REPORTING.Attribute where Name = 'Business Class'),            @WeccoSysId,        wp2.BusinessClass,
    (select AttributeId from REPORTING.Attribute where Name = 'Relationship 
  Manager Name'), @WeccoSysId,        wp2.RmFullName,       
    (select AttributeId from REPORTING.Attribute where Name = 'PbeName'),                   
 @WeccoSysId,       wp2.PbeFullName,            
    (select AttributeId from REPORTING.Attribute where Name = 'Overall Status'),        @WeccoSysId,        wp2.OverallStatus,
    (select AttributeId from REPORTING.Attribute where Name = 'Country of 
 Incorp'),          @WeccoSysId,        wp2.CountryInc,
    (select AttributeId from Reporting.Attribute where Name = 'Company 
 Reg'),               @WeccoSysId,        wp2.CompanyRegNum

    from        core.WeccoParty         wp1
  join  CORE.WeccoParty wp2                 on  wp1.CompanyRegNum       = 
 wp2.CompanyRegNum
  where wp1.OverallStatus = 'Onboarded'

                                     and wp2.OverallStatus = 'Onboarded'
                                     and wp1.CountryInc = wp2.CountryInc     
                                     and wp1.GtId <> wp2.GtId
                                     and wp1.CompanyRegNum is not null
                                     and wp2.CompanyRegNum is not null
                                     and wp1.CountryInc is not null
                                     and wp2.CountryInc is not null
                                     and wp1.CompanyRegNum NOT IN 
 ('0','.','n.a','n/a','n.a.','00000','unknown','Unknown','000000','00000000')
                                     and wp1.CompanyRegNum NOT LIKE('^0*0$')

回答(0)