Joins with grouping · 连接与分组
Reports: joins with grouping
The real power comes from combining what you have learned: join two tables, group the joined rows, and summarise each group with an aggregate.
A common report is "how much has each customer spent?" — join customers to their orders, group by customer, and SUM the totals.
报表:连接加分组
真正的威力来自把你学过的东西组合起来:连接两张表,把连接后的行分组,再用聚合函数为每组做汇总。
一个常见的报表是“每位顾客花了多少钱?”—— 把顾客连接到他们的订单,按顾客分组,再 SUM 出金额。
One row per customer
Reading it in order: join the tables, group the rows by customer, then for each group count the orders and add up the totals.
每位顾客一行
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
ORDER BY customer.name;
按顺序来读:先连接两张表,再按顾客把行分组,然后为每组数出订单数、加总金额。
Common mistakes
- Join first, then
GROUP BYto summarise the joined rows. - Qualify a column with its table name when both tables share it.
常见错误
- 先连接,再用
GROUP BY汇总连接后的行。 - 两张表都有的列,用表名限定它。
Joining tables · 连接表
An inner join drops rows that have no match. · 内连接会丢弃没有匹配的行。
For each customer who has orders, show their name, their number of orders as orders, and their total spend (2 d.p.) as spent. Join, group by customer.id, and order by name. · 对每位有订单的顾客,显示其 name、订单数(命名为 orders)和总消费额(两位小数,命名为 spent)。连接后按 customer.id 分组,并按 name 排序。
Click Run to see the output here. · 点击“运行”查看此处输出。
An INNER JOIN hid Ben and Dan, who have no orders. Use a LEFT JOIN to keep every customer, and COUNT(orders.id) so a customer with no orders shows 0. Order by name. · INNER JOIN 把没有订单的 Ben 和 Dan 藏了起来。用 LEFT JOIN 保留每一位顾客,并用 COUNT(orders.id),让没有订单的顾客显示为 0。按 name 排序。
Click Run to see the output here. · 点击“运行”查看此处输出。