MySQL查询：JOIN关联时无产品订单的SUM值显示为0的实现方案

修正后的SQL查询 #

SELECT
     d.id,
     d.number,
     dt.name AS type,
     sbj.name AS supplier,
     s.name AS stock,
     d.status,
     COALESCE(SUM(ds.qty * ds.price), 0) AS total,
     d.created_at AS created_at
FROM documents AS d
INNER JOIN subject AS sbj ON sbj.id = d.subject_id
INNER JOIN stock AS s ON s.id = d.stock_id
INNER JOIN document_type AS dt ON dt.id = d.document_type_id
LEFT OUTER JOIN document_items ds ON ds.document_id = d.id
GROUP BY d.id, d.number, dt.name, sbj.name, s.name, d.status, d.created_at
ORDER BY d.id DESC;

关键修改点说明 #

• 关联方式调整：将RIGHT OUTER JOIN改为LEFT OUTER JOIN（可简写为LEFT JOIN）。LEFT JOIN会保留documents表的所有记录，即使对应的document_items不存在，这样就能显示所有订单，包括无产品的订单。
• 添加分组语句：由于使用了聚合函数SUM，必须通过GROUP BY指定所有非聚合字段（即SELECT中除了SUM之外的所有列），确保每个订单的总价是独立计算的，不会出现所有订单总价合并的情况。
• COALESCE的正确作用：当订单无对应document_items时，SUM(ds.qty * ds.price)会返回NULL，COALESCE会将这个NULL转换为0，满足无产品订单显示0的需求。

原查询的问题根源 #

1. RIGHT OUTER JOIN的逻辑错误：该关联以document_items为基准，仅保留存在商品项的订单，导致无产品的订单被过滤。
2. 缺少GROUP BY：聚合函数SUM在无分组时会计算所有匹配记录的总和，无法得到单个订单的总价。

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