分组查询

In this section, you will learn below keywords of MySQL:

GROUP BY
HAVING
ORDER BY

Example table: user_profile

id	device_id	gender	age	university	gpa	active_days_within_30	question_cnt	answer_cnt
1	2138	male	21	Peking University	3.4	7	2	12
2	3214	male		Fudan University	4	15	5	25
3	6543	female	20	Peking University	3.2	12	3	30
4	2315	female	23	Zhejiang University	3.6	5	1	2
5	5432	male	25	Shandong University	3.8	20	15	70
6	2131	male	28	Shandong University	3.3	15	7	13
7	4321	male	28	Fudan University	3.6	9	6	52

note

active_days_within_30: 30 天内活跃天数
question_cnt: 发帖数量
answer_cnt: 回答数量

分组查询数据涉及到两个新 SELECT 语句子句：GROUP BY 子句和 HAVING 子句。我们已经知道了如何用 SQL 聚合函数汇总数据，但之前的计算中都是对于表的所有数据或匹配特定的 WHERE 子句的数据中进行，比如计算复旦大学学生的平均 gpa 情况，但如果我们想要返回每个学校的分别的平均 gpa 情况，应该怎么做呢？这时候就需要用到要讲的分组语句。

分组计算 GROUP BY

我们来看一个例子：

SELECT university, ROUND(AVG(gpa), 2) AS avg_gpa
FROM user_profile
GROUP BY university;

university	avg_gpa
Peking University	3.3
Fudan University	3.8
Zhejiang University	3.6
Shandong University	3.55

GROUP BY 子句指示 SQL 按 university 分组分别计算每个学校的平均 gpa 情况，从输出中可以看到，结果返回了每个大学的平均 gpa 数值。在使用 GROUP BY 时，有一些事项需要注意：

GROUP BY 子句可以包含任意数目的列，因而可以对分组进行嵌套，更细致地进行数据分组。
除聚集计算语句外，SELECT 语句中的每一列都必须在 GROUP BY 子句中同时给出。
如果分组列中包含具有 NULL 值的行，则 NULL 将作为一个分组返回。如果列中有多行 NULL 值，它们将分为一组。
GROUP BY 子句必须出现在 WHERE 子句之后，ORDER BY 子句之前。

分组过滤 HAVING

除了能用 GROUP BY 分组数据外，SQL 还允许在分组的结果下进行过滤，分组查询的结果不能简单的使用 WHERE 语句进行过滤，而需要使用专门的 HAVING 语句。我们计算了不同性别和不同学校分组下的用户 30 天内平均活跃天数情况，假如说我们只想要取出平均活跃天数在 10 天以上的分组，这时就可以用到 HAVING 语句，来看示例：

SELECT
    gender,
    university,
    COUNT(device_id) AS user_num,
    AVG(active_days_within_30) AS avg_active_days,
    AVG(question_cnt) AS avg_question_cnt
FROM user_profile
GROUP BY gender, university
HAVING AVG(active_days_within_30) > 10;

gender	university	user_num	avg_active_days	avg_question_cnt
male	Fudan University	2	12	5.5
female	Peking University	1	12	3
male	Shandong University	2	17.5	11

可以看到，这条 SELECT 语句中除最后一行外其他语法与上一道题完全一致。在最后一行中我们增加了 HAVING 子句，并搭配使用了 AVG(active_days_within_30) > 10 的条件，这种最终结果中就只会返回 30 天平均活跃天数大于 10 的分组。

tip

从上图可以看到 SELECT 在 HAVING 后才开始执行，这个时候 SELECT 后面列的别名只对后续的步骤生效，而对 SELECT 前面的步骤是无效的。所以如果你在 WHERE，GROUP BY，或 HAVING 后面使用列的別名均会报错。理论上来说，SELECT 中的聚合函数字段无法在 HAVING 中使用，但是 MySQL 会做自动优化的替换，无论定义在哪，聚合的操作执行一次，HAVING 里和 SELECT 里都可以使用。

分组排序 ORDER BY

如果想让分组查询的结果按照某个特殊的字段进行升序或降序排列，那么应该怎么做呢？分组查询结果也支持排序功能，所需要用到的语句是 ORDER BY ，来看语句示例：

SELECT university, AVG(active_days_within_30) as avg_active_days
FROM user_profile
GROUP BY university
ORDER BY avg_active_days;

按照学校分组分别计算每个学校用户的平均活跃天数情况，并按照平均活跃天数结果进行升序排列。

university	avg_active_days
Zhejiang University	5
Peking University	9.5
Fudan University	12
Shandong University	17.5

tip

ORDER BY 可以使用列的别名。

分组查询

分组计算 GROUP BY

分组过滤 HAVING

分组排序 ORDER BY

Quiz

Reference

分组计算 GROUP BY​

分组过滤 HAVING​

分组排序 ORDER BY​

Quiz​

Reference​

分组计算 GROUP BY

分组过滤 HAVING

分组排序 ORDER BY

Quiz

Reference