10、聚合函数、分组查询

1、聚合查询举例

查询students表一共有多少条记录为例，我们可以使用SQL内置的COUNT()函数查询：

SELECT COUNT(*) FROM students;

结果如下：

COUNT(*)

10

上诉结果返回的是一个二维表，我们可以给结果起一个别名

SELECT COUNT(*) as num FROM students;

结果如下：

num

10

COUNT(*)和COUNT(id)实际上是一样的效果。

另外注意，聚合查询同样可以使用WHERE条件，因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等：

SELECT COUNT(*) boys FROM students WHERE gender = 'M';

结果如下：

boys

5

 

2、常用聚合函数

函数	说明
SUM	计算某一列的合计值，该列必须为数值类型
AVG	计算某一列的平均值，该列必须为数值类型
MAX	计算某一列的最大值
MIN	计算某一列的最小值
COUNT	计数
DISTINCT	去重
CEILING	给某个数向上取整，即1.1为2， 2.9为3
FLOOR	给某个数向下取整，即2.2为2， 2.9为2

统计男生的平均成绩，我们用下面的聚合查询：

SELECT AVG(score) average FROM students WHERE gender = 'M';

结果如下：

average

81.4

再比如：

我要查询班级有哪些：

SELECT DISTINCT class_id from students;

class_id

1

2

3

要特别注意：如果聚合查询的WHERE条件没有匹配到任何行，COUNT()会返回0，而SUM()、AVG()、MAX()和MIN()会返回NULL

3、分组查询

SQL还提供了“分组聚合”的功能。我们观察下面的聚合查询，

执行这个查询，COUNT()的结果不再是一个，而是3个，这是因为，GROUP BY子句指定了按class_id分组，因此，执行该SELECT语句时，会把class_id相同的列先分组，再分别计算，因此，得到了3行结果。

SELECT COUNT(*) num FROM students GROUP BY class_id;

结果：

num

4

3

3

我们想统计各班的男生和女生人数：

SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;

class_id	gender	num
1	M	2
1	F	2
2	F	1
2	M	2
3	F	2
3	M	1

练习：

• 查询查出每个班级的平均分
• 查询查出每个班级男生和女生的平均分

答案：

• SELECT class_id, AVG(score) as avg_score FROM students GROUP BY class_id;
• SELECT class_id, gender, AVG(score) as avg_score FROM students GROUP BY class_id, gender;

接上面练习：

我们要查出每个班级的平均分，并且平均分大于85的班级是哪些？

有同学可能说，这还不简单

• SELECT class_id, AVG(score) as avg_score FROM students GROUP BY class_id WHERE AVG(score) > 85;

事实上这里会报错，因为分组查询之后已经有一个结果集，不能用WHERE，需要用HAVING

改为：

• SELECT class_id, AVG(score) as avg_score FROM students GROUP BY class_id HAVING AVG(score) > 85;

结果：

class_id	avg_score
1	86.5
3	89.33333333333333

顺道插一嘴（高频面试题，where和having区别）：

1，两者在select语句中都可以作为条件；

2，having是在分组之后对数据进行过滤，where是在分组之前对数据进行过滤；

3，having之后还可以跟聚合函数，而where是不可以的；

4，where用于精准查询，除了与like配合使用之外；

5，having的使用必须与group by 结合使用，否则报错。

4、小结

• 常用聚合查询有SUM、AVG、MAX、MIN、COUNT、DISTINCT、CEILING、FLOOR
• 分组查询用GROUP BY，如果要对分组查询之后的结果做筛选，需要用HAVING而不是用WHERE