多表关联统计分组：求兼容MariaDB的单SQL查询解决方案

这个问题的核心是多表连接时产生的重复行导致聚合统计失真——当你直接LEFT JOIN orders表时，同一个item如果对应多条订单记录，会把items和inventory_item的行重复输出，最终SUM计算时会把这些重复行的值多次累加，自然就出现了错误。

要解决这个问题，我们需要先对orders表按item_id做预聚合，得到每个商品的总订单量，再和其他表进行关联，这样就不会让items和inventory_item的行被重复渲染。以下是兼容MariaDB的单条SQL解决方案：

SELECT
  ii.inventory_id,
  i.group_id,
  SUM(i.min_amount) AS sum_min_amount,
  SUM(ii.amount) AS sum_inventory_amount,
  SUM(ii.amount * i.worth) AS sum_inventory_worth,
  COALESCE(SUM(o.total_orders), 0) AS sum_orders
FROM items i
INNER JOIN inventory_item ii ON i.id = ii.item_id
LEFT JOIN (
  -- 预聚合每个商品的总订单量
  SELECT item_id, SUM(amount) AS total_orders
  FROM orders
  GROUP BY item_id
) o ON i.id = o.item_id
GROUP BY ii.inventory_id, i.group_id;

关键细节说明： #

• 预聚合订单数据：子查询先把orders按item_id分组求和，确保每个商品只对应一行订单统计结果，避免连接时产生重复行。
• COALESCE函数：用来处理没有订单的商品，将sum_orders的NULL值替换为0，保证统计结果的完整性。
• 关联逻辑：保持items和inventory_item的INNER JOIN（因为你需要关联库存中的商品），再用LEFT JOIN关联预聚合后的订单数据，确保不会遗漏没有订单的商品。

这个写法在所有支持子查询的MariaDB版本中都能正常运行，完全符合你的单条SQL实现需求。

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