第14课:汇总统计及GROUP BY(2)

本节要点

过滤分组结果
排序分组结果
SELECT子句顺序

过滤分组结果

继续接着上一节课来讲GROUP BY分组统计。考虑这样一个查询需求:

(1)、如何获取教授15名以上学生的老师?

通过上节课的学习,我们知道,使用COUNT聚合函数配合GROUP BY分组,可以查询出每一个老师教授的学生数量。

但这里,需要将教授的学生数量查询出来后,过滤出教授的学生数量在15名以上的老师。那如何去过虑呢?

在SQL语言里,对分组后的结果进行过滤,需要使用HAVING关键字。

比如,对于上面这个查询需求,可以写出SQL语句如下:

SELECT teacher_id,count(*)
FROM student
GROUP BY teacher_id
HAVING count(*) > 15;

也就是说,在GROUP BY子句后面,跟上一个HAVING子句,限定COUNT(*)的结果大于15,这样就能取出教授学生数量在15个以上的老师编号了。

(2)、如何获取数学成绩平均分在70分及以上的班级?

SELECT
    class_id,avg(score)
FROM student
GROUP BY class_id
HAVING avg(score) >= 70;

(3)、如何获取每个班级的数学成绩平均分(不计算成绩在80分以下的学生且过滤掉平均分在90分以下的班级),以便比较不同班级的成绩?

SELECT
    class_id,avg(score)
FROM student
WHERE score > 80
GROUP BY class_id
HAVING avg(score) > 90;

针对这个查询需求,我们使用到了WHERE和HAVING来过滤数据。那WHERE和HAVING过滤数据,到底有什么区别呢?

从上面的SQL语句可以看到,WHERE子句写在GROUP BY子句的前面,它是对查询表的原始数据行进行过滤的。

而HAVING子句写在GROUP BY的后面,是GROUP BY分组统计完成后,对分组统计的结果再次进行过滤。

所以,WHERE和HAVING虽然都是过滤数据,但使用的场景还是不一样的。

排序分组结果

(1)、如何获取每个班级的数学成绩平均分(不计算成绩在80分以下的学生),然后以平均分从高到低排序?

针对这个查询需求,首先我们先要以class_id分组,计算出每个班级的数学成绩的平均分:

SELECT
    class_id,avg(score)
FROM student
WHERE score > 80
GROUP BY class_id;

这个SQL语句执行后,可以发现,查询出来的数据默认是按class_id来排序的。但我们需要按平均分从高到低排序,那么就需要使用ORDER BY关键字:

SELECT
    class_id,avg(score)
FROM student
WHERE score > 80
GROUP BY class_id
ORDER BY avg(score) DESC;

SELECT子句顺序

考虑一个复杂的查询需求:

(1)、如何获取每个班级的数学成绩平均分(不计算成绩在80分以下的学生且过滤掉平均分在90分以下的班级),然后以平均分从高到低排序?

SELECT
    class_id,avg(score)
FROM student
WHERE score > 80
GROUP BY class_id
HAVING avg(score) > 90
ORDER BY avg(score) DESC;

在这个SQL语句里,包括了SELECT子句,FROM子句,WHERE子句,GROUP BY子句,HAVING子句,ORDER BY子句,理解起来还是非常复杂的。而且,不同的子句,如果执行顺序不一样,查询出来的结果也会不一样。

在SELECT查询中,各子句的执行顺序如下表:

子句 作用 是否必须使用
SELECT 指定要返回的字段或表达式
FROM 指定检索数据来源 否。只在从表中取数时必须,其他情况下可不使用
WHERE 过滤行
GROUP BY 指定分组字段或表达式 否。只在分组计算使用聚集函数时必须,其他情况下不使用
HAVING 过滤分组
ORDER BY 指定排序字段或表达式

一定要记住各子句的执行顺序,它可能会影响到我们对查询结果的理解。

从上面这张表也可以看出,在SELECT查询中,SELECT子句是必须要有的,其他部分都可以没有。可以看下下面这句最简单的SQL查询:

SELECT 1;

课后习题:

1、对于第6课课后习题中的论坛注册用户表,请编写SQL完成以下查询。

(1)、统计2018年每个月份的注册用户数,并将注册用户数在100位以下的月份剔除,然后按顺序取出注册用户数最多的前3个月份及注册用户数。

(2)、统计注册用户数最少的10个省份。

【版权说明:仅允许非商业转载且请注明出处:Mac私塾 网址:http://macsishu.com】

发表评论

邮箱地址不会被公开。

Captcha Code