使用DISTINCT与子查询仍返回重复值：按slcitemnumber正确求和方案问询

你遇到的核心问题是分组时包含了存在差异的productlongname字段，导致同一个slcitemnumber被拆分成多条记录，无法得到准确的汇总结果。

当前查询结果（示例） #

原SQL语句 #

select distinct year(a.salesdate) 
  -- ,Zonename
,a.slcitemnumber
, a.productlongname
, sum(a.transactionamount)
from
  digital.ejtransdatafuelrewards a
  inner join (
    select
      distinct corporatebrandtype,
      productnumber
    from
      digital.dimproduct
    where
      corporatebrandtype = 'PB'
      and enddate > '2020-01-01'
      group by
      corporatebrandtype,
      productnumber
  ) b
 on a.slcitemnumber = b.productnumber
where
  salesdate between '2021-1-1'
  and '2021-12-31'
  and a.zonename not in ('Team 2')
  and a.slcitemnumber != '-99999'
  and char_length(slcitemnumber) = 6
  and a.slcitemnumber is not null
group by
  a.slcitemnumber
,year(a.salesdate)
,a.productlongname
having
  sum(transactionamount) > 0 

解决方案 #

方案1：仅按关键维度汇总（不需要显示产品名称） #

直接移除productlongname的分组和查询，只保留销售日期年份和slcitemnumber作为分组维度，就能得到每个slcitemnumber的总交易金额：

select 
  year(a.salesdate) as 销售日期年份,
  a.slcitemnumber,
  sum(a.transactionamount) as 交易金额汇总
from
  digital.ejtransdatafuelrewards a
inner join (
  select
    productnumber
  from
    digital.dimproduct
  where
    corporatebrandtype = 'PB'
    and enddate > '2020-01-01'
  group by
    productnumber
) b on a.slcitemnumber = b.productnumber
where
  salesdate between '2021-01-01' and '2021-12-31'
  and a.zonename not in ('Team 2')
  and a.slcitemnumber != '-99999'
  and char_length(a.slcitemnumber) = 6
  and a.slcitemnumber is not null
group by
  year(a.salesdate),
  a.slcitemnumber
having
  sum(a.transactionamount) > 0

优化说明：子查询里的distinct和corporatebrandtype分组可以去掉，因为已经通过where过滤了corporatebrandtype='PB'，只需要按productnumber去重即可。

方案2：保留产品名称，选取同一编码下的任意名称 #

如果需要显示产品名称，可以用MAX()或MIN()聚合函数，从同一slcitemnumber的多个名称中选取一个，确保每个编码只显示一条记录：

select 
  year(a.salesdate) as 销售日期年份,
  a.slcitemnumber,
  MAX(a.productlongname) as 产品全称, -- 也可以用MIN()，根据需求选择
  sum(a.transactionamount) as 交易金额汇总
from
  digital.ejtransdatafuelrewards a
inner join (
  select
    productnumber
  from
    digital.dimproduct
  where
    corporatebrandtype = 'PB'
    and enddate > '2020-01-01'
  group by
    productnumber
) b on a.slcitemnumber = b.productnumber
where
  salesdate between '2021-01-01' and '2021-12-31'
  and a.zonename not in ('Team 2')
  and a.slcitemnumber != '-99999'
  and char_length(a.slcitemnumber) = 6
  and a.slcitemnumber is not null
group by
  year(a.salesdate),
  a.slcitemnumber
having
  sum(a.transactionamount) > 0

方案3：从维度表获取标准产品名称 #

如果digital.dimproduct表中存储了productnumber对应的标准产品名称，建议直接关联该表获取统一名称，从根源避免源表的名称差异：

select 
  year(a.salesdate) as 销售日期年份,
  a.slcitemnumber,
  d.productlongname as 产品全称, -- 使用维度表的标准名称
  sum(a.transactionamount) as 交易金额汇总
from
  digital.ejtransdatafuelrewards a
inner join digital.dimproduct d 
  on a.slcitemnumber = d.productnumber
where
  d.corporatebrandtype = 'PB'
  and d.enddate > '2020-01-01'
  and a.salesdate between '2021-01-01' and '2021-12-31'
  and a.zonename not in ('Team 2')
  and a.slcitemnumber != '-99999'
  and char_length(a.slcitemnumber) = 6
  and a.slcitemnumber is not null
group by
  year(a.salesdate),
  a.slcitemnumber,
  d.productlongname
having
  sum(a.transactionamount) > 0

注意：需确保digital.dimproduct中productnumber与productlongname是一一对应的，否则可能仍有重复，此时可以结合方案2的聚合函数处理。

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