多表关联分组时SQL聚合函数计算偏差及多币种统计方案咨询

核心思路 #

问题本质是两个独立的一对多关系关联后产生笛卡尔积重复计数，加上实收金额未按币种区分导致聚合无意义，两步即可解决：

1. 先分别对sale_lines和cash_transactions做预聚合，避免关联后的重复计数
2. 对cash_transactions预聚合时增加received_currency_id作为分组维度，保证只有同币种金额才会被求和

方案1：SQL层直接输出结构化统计结果 #

这个方案直接在SQL层面完成所有合法聚合，返回的结果已经是正确统计值，不需要额外PHP处理：

SELECT
  -- 销售结算币种信息
  s.currency_items_sold_in AS sale_currency,
  c.iso_code AS sale_currency_code,
  -- 该币种下的总销售额（sale_lines求和，无重复）
  SUM(sl_agg.total_sale_amount) AS total_sale_amount,
  -- 该币种下的兑换后总金额（和销售币种一致，可直接求和）
  SUM(ct_agg.total_converted_per_sale) AS total_converted_amount,
  -- 实收币种信息
  ct_agg.received_currency_id,
  c2.iso_code AS received_currency_code,
  -- 对应实收币种的总金额
  SUM(ct_agg.total_received_per_currency) AS total_received_amount
FROM sale s
-- 关联币种表拿到销售币种代码
LEFT JOIN currency c ON c.iso_number = s.currency_items_sold_in
-- sale_lines按sale_id预聚合，避免笛卡尔积
LEFT JOIN (
  SELECT
    sale_id,
    SUM(price_paid * quantity) AS total_sale_amount
  FROM sale_lines
  GROUP BY sale_id
) sl_agg ON sl_agg.sale_id = s.id
-- cash_transactions按sale_id+实收币种预聚合，保证同币种求和
LEFT JOIN (
  SELECT
    sale_id,
    received_currency_id,
    SUM(converted_amount) AS total_converted_per_sale,
    SUM(received_amount) AS total_received_per_currency
  FROM cash_transactions
  GROUP BY sale_id, received_currency_id
) ct_agg ON ct_agg.sale_id = s.id
-- 关联币种表拿到实收币种代码
LEFT JOIN currency c2 ON c2.iso_number = ct_agg.received_currency_id
-- 最终按销售币种+实收币种分组
GROUP BY s.currency_items_sold_in, c.iso_code, ct_agg.received_currency_id, c2.iso_code;

运行上述SQL返回的结果会包含每个销售币种下，不同实收币种的统计数据，所有求和结果都是正确的，不会有重复计数，也不会出现不同币种混加的情况。

方案2：输出明细级聚合结果给PHP处理 #

如果你需要更灵活的后续统计，可以先按sale维度返回预聚合结果，PHP侧自行汇总：

SELECT
  s.id AS sale_id,
  s.currency_items_sold_in AS sale_currency,
  sl_agg.total_sale_amount,
  ct_agg.received_currency_id,
  ct_agg.total_received_per_currency,
  ct_agg.total_converted_per_sale
FROM sale s
LEFT JOIN (
  SELECT sale_id, SUM(price_paid * quantity) AS total_sale_amount FROM sale_lines GROUP BY sale_id
) sl_agg ON sl_agg.sale_id = s.id
LEFT JOIN (
  SELECT 
    sale_id, 
    received_currency_id, 
    SUM(received_amount) AS total_received_per_currency,
    SUM(converted_amount) AS total_converted_per_sale
  FROM cash_transactions GROUP BY sale_id, received_currency_id
) ct_agg ON ct_agg.sale_id = s.id;

拿到结果后在PHP中按销售币种、实收币种为key做数组累加即可得到任意维度的统计值。

结果验证 #

针对你提供的测试数据，正确统计结果如下：

• 销售币种为DKK（208）的总销售额：500，兑换后总金额：500，实收金额分别为DKK 200、SEK 400
• 销售币种为SEK（752）的总销售额：200，兑换后总金额：200，实收金额分别为NOK 150、DKK 100
  完全符合测试数据的实际业务含义，没有重复计数也没有跨币种求和的问题。

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