内部联接是否排除重复项

Question

my query：

  select 
  SUM(payments.paid_amt) as paid_amt, 
  SUM(payments.copay_amt) as copay_amt,
  SUM(payments.CO_INSURANCE_AMT) as co_ins_amt,
  payor_group.PAYOR_GROUP as payor_group,
  DATEPART(MM,accession.XIFIN_LOAD_DATE) as load_month,
  DATEPART(yyyy,accession.XIFIN_LOAD_DATE) as load_year,
  accession.ACCESSION_ID

  from 
  [F_PAYOR_PAYMENTS_monthly] payments
  join D_PAYOR payor
  on payor.PAYOR_ID=payments.PAYMENT_PAYOR_ID
  join D_PAYOR_GROUP payor_group
  on payor.PAYOR_GROUP_KEY=payor_group.PAYOR_GROUP_KEY
  join F_ACCESSION_daily accession
  on accession.ACCESSION_ID=payments.ACCESSION_ID
  where accession.XIFIN_LOAD_DATE between '20120501' and '20120531'
  group by  payor_group.PAYOR_GROUP, DATEPART(MM,accession.XIFIN_LOAD_DATE),
  DATEPART(yyyy,accession.XIFIN_LOAD_DATE),accession.ACCESSION_ID

出于某种原因，SUMs多次计算相同的accession.ACCESSION_ID。

当我执行所有这些联接时，我担心的是我可能会多次SUMming相同的值。

如何确保只计算一次相同的accession.ACCESSION_ID？

非常感谢您的帮助和指导。

Answer 1

您的付款表中可能有多个行具有

相同ACCESSION_ID，其中包含不同的付款人组。由于您也按payor_group分组，因此最终会得到多个入藏 ID。

但是，我们需要查看一些样本数据才能确定。

Answer 2

问题是数据的重复。

为了找出导致问题的JOIN，我会依次注释每个并检查结果。创建额外数据通常很明显。

如果没有看到数据，这很棘手，但是，例如，如果加入表中有一个地理键，而付款表中没有，并且如果两个地理键发生了ACCESSION_ID，那么在ACCESSION_ID上将这些表连接在一起时，您将获得两行数据。

一种解决方案可能是在 OP 中点击查询之前对 #temp 表进行一些初始分组，另一种选择是如下所示的 CTE。

;with accession (XIFIN_LOAD_DATE, ACCESSION_ID)
    as 
    (
    select 
        XIFIN_LOAD_DATE
        ,ACCESSION_ID
    from F_ACCESSION_daily
    group by
         XIFIN_LOAD_DATE
         ,ACCESSION_ID
    )
    , payments (PAYMENT_PAYOR_ID, ACCESSION_ID, paid_amt, copay_amt, CO_INSURANCE_AMT)
    as 
    (
    select 
        PAYMENT_PAYOR_ID
         ,ACCESSION_ID
        ,sum(paid_amt) paid_amt
        ,sum(copay_amt) copay_amt
        ,sum(CO_INSURANCE_AMT) CO_INSURANCE_AMT
    from F_PAYOR_PAYMENTS_monthly
    group by
         PAYMENT_PAYOR_ID
         ,ACCESSION_ID
    )
select 
  SUM(payments.paid_amt) as paid_amt, 
  SUM(payments.copay_amt) as copay_amt,
  SUM(payments.CO_INSURANCE_AMT) as co_ins_amt,
  payor_group.PAYOR_GROUP as payor_group,
  DATEPART(MM,accession.XIFIN_LOAD_DATE) as load_month,
  DATEPART(yyyy,accession.XIFIN_LOAD_DATE) as load_year,
  accession.ACCESSION_ID
from 
  payments
  join D_PAYOR payor
  on payor.PAYOR_ID=payments.PAYMENT_PAYOR_ID
  join D_PAYOR_GROUP payor_group
  on payor.PAYOR_GROUP_KEY=payor_group.PAYOR_GROUP_KEY
  join accession
  on accession.ACCESSION_ID=payments.ACCESSION_ID
where accession.XIFIN_LOAD_DATE between '20120501' and '20120531'
group by  
  payor_group.PAYOR_GROUP
  , DATEPART(MM,accession.XIFIN_LOAD_DATE),
  DATEPART(yyyy,accession.XIFIN_LOAD_DATE),accession.ACCESSION_ID