使用JOIN关联表时如何编写SQL查询实现数据去重

• 过滤条件错误：子查询中WHERE Market IS NOT NULL直接筛掉了Primary_Brand为Kornet、Market为NULL、Type为CWB的行，导致预期输出中的对应行缺失
• 聚合逻辑错误：使用sum(distinct Spent)计算总支出会遗漏金额相同的支出项，同时未对空市场的支出做特殊处理，不符合预期输出规则
• 冗余分组：JOIN后重复对所有返回字段做GROUP BY，无实际作用还放大了数据重复的问题
• 关联逻辑冗余：用RIGHT JOIN关联Food表没必要，且Food表按Primary_Brand_Key是唯一对应，不会引发重复

SELECT 
  t.Primary_Brand_Key as Primary_Brand,
  t.Market,
  t.Type,
  f.Food,
  t.TV_Spends,
  CASE WHEN t.Market IS NULL THEN 0 ELSE t.Print_Spends END AS Print_Spends,
  COALESCE(t.TV_Spends, 0) + COALESCE(CASE WHEN t.Market IS NULL THEN 0 ELSE t.Print_Spends END, 0) AS Total_Spends
FROM (
  SELECT 
    Primary_Brand_Key,
    Market,
    Type,
    SUM(CASE WHEN Medium = 'TV' THEN Spent END) AS TV_Spends,
    SUM(CASE WHEN Medium = 'Print' THEN Spent END) AS Print_Spends
  FROM Brand
  GROUP BY Primary_Brand_Key, Market, Type
) t
LEFT JOIN Food f ON t.Primary_Brand_Key = f.Primary_Brand_Key
ORDER BY f.Food DESC, t.Market DESC;

• 移除了子查询中WHERE Market IS NOT NULL的过滤条件，保留Market为NULL的行
• 移除了冗余的外层GROUP BY，子查询按Primary_Brand_Key、Market、Type分组后已经是唯一行，关联Food表不会产生重复
• 总支出改为TV和Print支出相加计算，避免sum(distinct)的统计错误
• 按预期要求对Market为NULL的行做特殊处理，Print支出显示为0，总支出为0
• 关联逻辑改为LEFT JOIN，以Brand聚合后的结果为基准关联Food表，保证所有需要的行都保留

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