真正的威力来自把你学过的东西组合起来:连接两张表,把连接后的行分组,再用聚合函数为每组做汇总。
一个常见的报表是“每位顾客花了多少钱?”—— 把顾客连接到他们的订单,按顾客分组,再 SUM 出金额。
SELECT customer.name,
COUNT(*) AS orders,
ROUND(SUM(orders.total), 2) AS spent
FROM customer
INNER JOIN orders ON customer.id = orders.customer_id
GROUP BY customer.id, customer.name
ORDER BY customer.name;
按顺序来读:先连接两张表,再按顾客把行分组,然后为每组数出订单数、加总金额。
内连接 INNER JOIN 会丢弃 Ben 和 Dan,因为他们没有订单。左连接 LEFT JOIN 会保留每位客户,并为没有匹配的订单填入 NULL 值。COUNT(*) 会把这个占位行算作一行;而 COUNT(orders.id) 会跳过它的 NULL id,正确地给出零。当不存在非 NULL 的总计时,函数 SUM 返回 NULL,因此应使用函数 COALESCE(..., 0) 来生成零支出报告。
若要只统计金额至少为 25 的订单同时保留所有客户,请将订单条件置于 ON 中:
SELECT customer.name, COUNT(orders.id) AS qualifying_orders,
COALESCE(ROUND(SUM(orders.total), 2), 0) AS spent
FROM customer
LEFT JOIN orders ON customer.id = orders.customer_id AND orders.total >= 25
GROUP BY customer.id, customer.name
ORDER BY customer.name;
结果为 Ada:1 和 25;Ben:0 和 0;Cara:1 和 40;Dan:0 和 0。将条件 orders.total >= 25 移至 WHERE 会导致 Ben 和 Dan 被移除:他们的 NULL 总计不满足该比较。还需按 id 分组,以便同名客户保持分开。
- 先连接,再用
GROUP BY汇总连接后的行。 - 两张表都有的列,用表名限定它。
连接表
内连接会丢弃没有匹配的行。
难度 4/5 · 今日首次完成可获 280 金币及少量额外奖励
对每位有订单的顾客,显示其 name、订单数(命名为 orders)和总消费额(两位小数,命名为 spent)。连接后按 customer.id 分组,并按 name 排序。
点击“运行”查看此处输出。
难度 4/5 · 今日首次完成可获 280 金币及少量额外奖励
INNER JOIN 把没有订单的 Ben 和 Dan 藏了起来。用 LEFT JOIN 保留每一位顾客,并用 COUNT(orders.id),让没有订单的顾客显示为 0。按 name 排序。
点击“运行”查看此处输出。
难度 4/5 · 今日首次完成可获 280 金币及少量额外奖励
保留每位顾客,包括没有合格订单的顾客。显示 name、total > 30 的订单数(命名为 orders)和这些订单的总额(命名为 spent,保留两位小数,没有则为 0)。按姓名排序。条件的位置必须保留未匹配的顾客。
点击“运行”查看此处输出。