SQL中LEFT JOIN结合聚合函数查询得到错误值如何解决

关联后金额虚高是典型的一对多关联行膨胀问题：

• 你使用的关联键cost_center_id + ds在右表d_employee_plus中不唯一，同一个成本中心+分区日期下存在多条重复记录
• 左表采购订单的每一行，匹配到右表N条重复记录时，就会被复制为N行，后续SUM计算时会把重复的金额重复累加，最终结果远大于实际值
• 举个直观例子：某成本中心对应采购金额1000元，右表中该成本中心有3条重复映射记录，关联后会生成3行金额为1000的记录，SUM结果变为3000，是实际值的3倍
• 额外注意：你原查询SELECT字段写的是po.po_cost_center_id，但关联条件用的是po.cost_center_id，属于字段名笔误，执行时可能直接报错，需要统一字段名。

优先推荐先聚合采购数据、再关联去重后的映射表的方案，性能更好也能完全避免膨胀问题：

WITH po_cost_center_agg AS (
  -- 第一步：先按成本中心聚合出准确的支出金额，这一步结果和你关联前的正确统计值完全一致
  SELECT
    cost_center_id,
    SUM(amount_ordered) AS total_amount
  FROM purchase_orders
  WHERE ds = (SELECT MAX(ds) FROM purchase_orders)
  GROUP BY cost_center_id
)
SELECT
  p.cost_center_id,
  e.product_group_name,
  p.total_amount
FROM po_cost_center_agg p
LEFT JOIN (
  -- 第二步：对映射表去重，保证每个成本中心只对应唯一的产品组
  SELECT DISTINCT cost_center_id, product_group_name
  FROM d_employee_plus
  WHERE ds = (SELECT MAX(ds) FROM purchase_orders)
) e
ON p.cost_center_id = e.cost_center_id

如果需要保留采购订单表明细粒度的关联，也可以直接在关联前对右表去重，避免行复制：

SELECT
  po.cost_center_id,
  ep.product_group_name,
  SUM(po.amount_ordered) AS total_amount
FROM purchase_orders po
LEFT JOIN (
  SELECT DISTINCT cost_center_id, ds, product_group_name
  FROM d_employee_plus
) ep 
  ON po.cost_center_id = ep.cost_center_id 
  AND ep.ds = po.ds
WHERE
  po.ds = (SELECT MAX(ds) FROM purchase_orders)
GROUP BY 1,2

如果用了上述方法还是存在金额不准的问题，先执行以下语句检查映射表是否存在一个成本中心对应多个不同产品组的脏数据，这类数据需要和业务方确认正确映射规则后再清洗：

SELECT
  cost_center_id,
  ds,
  COUNT(DISTINCT product_group_name) AS match_pg_count
FROM d_employee_plus
GROUP BY cost_center_id, ds
HAVING match_pg_count > 1

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