GROUP BY 分组

第 8 课，共 21 课 · 第 2 部分 · 汇总 · 约 15 分钟

上一课的聚合函数把整张表算成一行。可常常要的是「每个……各有多少」：每个分类有几件商品？用 WHERE 一个分类一个分类地数，可以，但很麻烦：

main.sql

1SELECT COUNT(*) FROM products WHERE category = '厨房';2SELECT COUNT(*) FROM products WHERE category = '文具';

输出

第 1 行的语句

COUNT(*)
2

1 行

第 2 行的语句

COUNT(*)
2

1 行

4 个分类就要写 4 条，分类多了更写不完。GROUP BY 一条就够。

GROUP BY

GROUP BY 列 把这一列值相同的行放进同一组，聚合函数再在每一组里各算一次：

main.sql

1SELECT category, COUNT(*)2FROM products3GROUP BY category4ORDER BY category;

输出

category	COUNT(*)
厨房	2
数码	4
文具	2
箱包	2

4 行

点单步看看：先拿到 10 件商品，GROUP BY 把它们按分类分成 4 堆，每一堆变成结果里的一行，COUNT(*) 数的是这一堆有几行。

ORDER BY 不能省：MySQL 分完组，不保证按什么顺序给你。汉字按编码排，不是按拼音或笔画。

每组一行

一个 SELECT 里可以放好几个聚合函数，每个都在每组里算一次：

main.sql

1SELECT category,2    COUNT(*) AS product_count,3    MIN(price) AS cheapest,4    MAX(price) AS dearest,5    SUM(stock) AS total_stock6FROM products7GROUP BY category8ORDER BY category;

输出

category	product_count	cheapest	dearest	total_stock
厨房	2	9.90	12.00	100
数码	4	8.50	59.00	245
文具	2	4.50	6.00	200
箱包	2	11.00	39.90	40

4 行

分组的列（category）每组只有一个值，所以可以放进 SELECT；聚合函数每组也只算出一个值。结果的每一行就是一组。

给结果排个名

每个客户下了几单？按客户编号分组，再按单数从多到少排，单数一样的按编号排：

main.sql

1SELECT customer_id, COUNT(*) AS order_count2FROM orders3GROUP BY customer_id4ORDER BY order_count DESC, customer_id;

输出

customer_id	order_count
1	3
2	2
4	2
5	2
3	1
7	1
8	1

7 行

1 号客户下了 3 单，最多。ORDER BY 可以用 SELECT 里起的别名，为什么可以，第 10 课细讲。

没下过单的 6、9、10 号客户不在结果里：订单表里根本没有他们的行，也就分不出他们的组。

每个订单多少钱

订单明细表里，一个订单有好几行。按 order_id 分组，把每组的「件数 × 单价」加起来，就是每个订单的金额：

main.sql

1SELECT order_id,2    SUM(quantity) AS items,3    SUM(quantity * unit_price) AS total4FROM order_items5GROUP BY order_id6ORDER BY order_id;

输出

order_id	items	total
1	4	19.50
2	1	39.90
3	3	76.00
4	1	15.00
5	4	43.80
6	5	22.50
7	3	61.90
8	1	59.00
9	4	21.00
10	2	23.50
11	1	12.00
12	4	39.60

12 行

18 行明细变成了 12 行，一个订单一行。

先猜一猜

订单的状态只有已发货、待发货、已取消三种。结果有几行？

1SELECT status, COUNT(*)2FROM orders3GROUP BY status;

GROUP BY status 把状态一样的订单放进一组，3 种状态就是 3 组，每组一行。至于这 3 行谁先谁后，没写 ORDER BY 就说不准。

按几列分组

GROUP BY 后面可以写好几列，这几列的值全都一样的行才是一组。每个客户的订单，再按状态分开数：

main.sql

1SELECT customer_id, status, COUNT(*)2FROM orders3GROUP BY customer_id, status4ORDER BY customer_id, status;

输出

customer_id	status	COUNT(*)
1	已发货	2
1	待发货	1
2	已发货	1
2	待发货	1
3	已发货	1
4	已取消	1
4	待发货	1
5	已发货	2
7	已发货	1
8	已发货	1

10 行

1 号客户有 2 单已发货、1 单待发货，分成了两行。

NULL 也自成一组。员工表里按上级分组，数每个人管着几个人：

main.sql

1SELECT manager_id, COUNT(*)2FROM employees3GROUP BY manager_id4ORDER BY manager_id;

输出

manager_id	COUNT(*)
NULL	1
1	2
2	2
3	1

4 行

manager_id 是 NULL 的那一组就是没有上级的经理本人。

SELECT 里能放什么

分了组以后，SELECT 里只能放两种东西：分组的列和聚合函数。放别的列就会报错：

main.sql

1SELECT category, name, COUNT(*)2FROM products3GROUP BY category;

输出

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'town.products.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

「name」没有写在 GROUP BY 里，每一组里它可能有好几个不同的值，不知道该显示哪一个。把它加进 GROUP BY，或者用 MAX(name) 这类聚合函数。

ERROR 1055：name 不在 GROUP BY 里。数码这一组有 4 件商品、4 个名字，结果却只有一行，显示哪个名字？MySQL 不替你挑。

改法看你要什么：要每组的数，就去掉 name；要每件商品一行，就不该分组；要每组「一个」名字，就用聚合函数挑一个，比如 MIN(name)。

小技巧： 有的数据库（还有很老的 MySQL）不报这个错，随便挑一个名字给你。结果看着没错，其实是碰巧，别依赖它。

练一练

写好查询，点检查。提示随便看，实在想不出来再看答案。

练习 1

每个城市的客户

查出每个城市有几个客户：两列 city 和 customers，按 city 排序。

要显示

city	customers
上海	3
北京	2
广州	2
成都	2
杭州	1

5 行

exercise1.sql

1SELECT city FROM customers;

输出

点「运行」或按 Ctrl+Enter

练习 2

每件商品卖了几件

从 order_items 里查出每件商品一共卖了几件：两列 product_id 和 sold，按 product_id 排序。

要显示

product_id	sold
1	10
2	3
3	2
4	3
5	2
7	3
8	6
9	2
10	2

9 行

exercise2.sql

1SELECT product_id, quantity FROM order_items;

输出

点「运行」或按 Ctrl+Enter

台灯（6 号）没人买过，所以不在结果里。

练习 3

修好它：ERROR 1055

想查每个职位有几个人、最高工资多少，按最高工资从高到低排，却报了 ERROR 1055。改好它，列是 title、COUNT(*)、MAX(salary)（ORDER BY 里也可以写聚合函数，按每组算出的值排）。

要显示

title	COUNT(*)	MAX(salary)
经理	1	6000
销售主管	1	4500
仓库主管	1	4200
销售	2	3300
打包员	1	2800

5 行

exercise3.sql

1SELECT title, name, COUNT(*), MAX(salary)2FROM employees3GROUP BY title4ORDER BY MAX(salary) DESC;

输出

点「运行」或按 Ctrl+Enter

这一课学会了

GROUP BY 列 把值相同的行分成一组，聚合函数在每组里各算一次，每组一行。
GROUP BY 后面可以写几列，这几列都一样才是一组；NULL 也自成一组。
分组后的顺序不一定，要用 ORDER BY 定下来。
SELECT 里只能放分组的列和聚合函数，放别的列会得到 ERROR 1055。