第 4 站练习答案

← 本站索引

保留零订单客户

SELECT c.customer_id,
       COUNT(o.order_id) AS paid_count,
       COALESCE(SUM(o.total_amount), 0) AS paid_total
FROM customer AS c
LEFT JOIN sales_order AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid'
GROUP BY c.customer_id;

过滤条件必须留在 ON 中。

防止多对多放大

WITH item_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS item_total
  FROM order_item GROUP BY order_id
),
payment_totals AS (
  SELECT order_id, SUM(amount) AS paid_total
  FROM payment GROUP BY order_id
)
SELECT o.order_id, i.item_total, p.paid_total
FROM sales_order AS o
LEFT JOIN item_totals AS i ON i.order_id = o.order_id
LEFT JOIN payment_totals AS p ON p.order_id = o.order_id;

payment 是扩展练习表。核心方法是分别聚合到一行一个订单，再连接，而不是让订单项和支付明细先相乘。

加码题解：连接基数与报表

SELECT o.id, COALESCE(SUM(i.quantity * i.unit_price), 0) AS total
FROM orders o
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY o.id;

关键推理

LEFT JOIN 保留零明细订单，COALESCE 明确空聚合语义；更复杂报表先在各明细粒度聚合。

可替代方案

把账本事实落成日汇总表，降低高频报表对在线表的压力。

典型错误

多个一对多表直接连接后 SUM，导致金额成倍放大。

复盘时请附上版本、隔离级别、执行计划或恢复位置中的相关证据；没有证据的“看起来正确”不算完成。