连接基数与可信报表

← 本站索引 ｜ 练习答案

可运行 SQL

USE mysql_course;

SELECT
  c.customer_id,
  c.display_name,
  COUNT(o.order_id) AS paid_order_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, c.display_name
ORDER BY c.customer_id;

WITH item_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS item_total
  FROM order_item
  GROUP BY order_id
)
SELECT o.order_id, o.total_amount, i.item_total
FROM sales_order AS o
JOIN item_totals AS i ON i.order_id = o.order_id
WHERE o.total_amount <> i.item_total;

机制解释

LEFT JOIN 先保留左侧客户。把右表状态过滤放在 ON 中，只限制匹配订单；放到 WHERE 会删除 NULL 扩展行，使结果表现得像内连接。

COUNT(column) 忽略 NULL，COUNT(*) 统计行。SUM 没有输入行时可能返回 NULL，因此展示层常用 COALESCE，但“无数据”是否等于零必须由业务定义。

ONLY_FULL_GROUP_BY 阻止查询选择无法由分组键唯一确定的列。它在保护结果语义，不应通过关闭 SQL mode 逃避建模问题。

真实边界

• 报表必须声明金额粒度、时间边界、时区和取消/退款规则。
• JOIN 正确性先于性能；索引只能加速错误答案。
• 浮点或跨币种金额不能直接求和；金额、币种和汇率时间点必须共同建模。

练习

1. 写出保留零订单客户、仅统计 paid 订单的查询。
2. 构造订单、订单项和支付的一对多乘积，解释重复金额来源。
3. 找出订单头金额与订单项汇总不一致的数据。

验收清单

• 能明确说明每个结果行的业务粒度。
• 零订单客户不会被右表过滤意外删除。
• 多个一对多关系不会让金额重复累计。

完成后核对答案

深度加餐：报表错误通常是连接基数先错了

先算每个 JOIN 输入输出的行数，再写 SUM；否则金额会因为一对多连接被重复放大。

先做一个可观察实验

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

预期：先按订单聚合明细，再连接订单主表；没有明细的订单仍显示 0，而不是被 INNER JOIN 吞掉。

原理下潜与工程边界

JOIN 只负责组合行，聚合负责定义粒度。函数依赖决定 GROUP BY 是否合理，ONLY_FULL_GROUP_BY 能帮助发现“选了未分组列”的歧义。

COUNT(*) 与 COUNT(column) 对 NULL 不同；多条一对多连接会形成笛卡尔放大；LEFT JOIN 后在 WHERE 过滤右表列会意外变成 INNER JOIN。

反例排错路线

逐步执行 CTE/子查询并统计行数；为每张表写主键唯一性检查；金额类报表补一条与业务账本对账的总和断言。

练习加码（仍不提供答案）

加码：写一份客户月度收入报表，既要包含零收入客户，又要避免订单与退款明细互相放大。

验收时请补充：一份可复现的环境/数据前提、正常结果、失败结果以及可回滚或清理动作。

继续查阅：MySQL 8.4 参考手册（外部资料已收录于证据账本） · InnoDB 文档（外部资料已收录于证据账本）