在select中显示临时表中的字段

Question

我有以下SQL。问题是它只在输出中显示以下内容的结果: wp.gtid、wp.first_name等，我也想显示来自wpe.gtid、wpe.first_name等的结果。这样我就可以很容易地看到并排的字段的比较。

with dups as (
    select 
          wp.GtId
        from CORE.WeccoParty wp
    where exists (select 1
                  from CORE.WeccoParty wpe
                -- where wp.Tin = wpe.Tin
                where wp.FirstName = wpe.LastName
                  and   wp.GtId     <> wpe.GtId    
              )
      )
       select distinct
      wp.GtId, 
      wp.CrmPartyId, 
      wp.LegalName, 
      wp.BusinessClass, 
      wp.RmFullName, 
      wp.PbeFullName, 
      wp.OverallClientStatus, 
     wp.OverallRpStatus, 
       wp.FirstName,
      wp.LastName,
      wp.Tin


from CORE.WeccoParty wp
join dups d on d.GtId = wp.GtId
order by 1,2

Answer 1

K1205，当我检查您的代码时，它似乎识别出重复条目具有相同的FirstName但有不同的GtId值。

因此，也许您可以使用Row_Number()函数和分区by子句

您能检查一下下面的SQL吗？

select 
    ROW_NUMBER() over (Partition By wp.FirstName Order By wp.GtId) as rn,
    wp.GtId, 
    wp.CrmPartyId, 
    wp.LegalName, 
    wp.BusinessClass, 
    wp.RmFullName, 
    wp.PbeFullName, 
    wp.OverallClientStatus, 
    wp.OverallRpStatus, 
    wp.FirstName,
    wp.LastName,
    wp.Tin
from CORE.WeccoParty wp
order by wp.FirstName, rn

我希望下面的CTE查询可以帮助您解决来自重复项的数据( same和gtid)，这些数据在同一个名字的第一个条目之后作为新列添加。

with rawdata as (
    select 
        ROW_NUMBER() over (Partition By wp.FirstName Order By wp.GtId) as rn,
        wp.GtId, 
        wp.CrmPartyId, 
        wp.LegalName, 
        wp.BusinessClass, 
        wp.RmFullName, 
        wp.PbeFullName, 
        wp.OverallClientStatus, 
        wp.OverallRpStatus, 
        wp.FirstName,
        wp.LastName,
        wp.Tin
    from WeccoParty wp
)
select t1.*, t2.GtId, t2.FirstName 
from rawdata as t1
inner join rawdata as t2
    on t1.FirstName = t2.FirstName and
       t1.rn <> t2.rn
where t1.rn = 1
order by t1.FirstName, t2.rn