本文以牛客网在线题库的SQL33题(找出每个学校GPA最低的同学)为例进行讲解
文章目录
前言
一、什么是聚集函数
聚集函数的使用规则
二、group by和having的使用
1.group by的使用
2.having的使用
总结
前言
很多人对分组函数只知其一不知其二,用group by写的代码有的时候也不知道为什么会报错,那可能是因为你并不知道以下的使用规则
一、什么是聚集函数
- count(),返回指定列中数据的个数
- sum(),返回指定列中数据的总和
- avg(),返回指定列中数据的平均值
- min(),返回指定列中数据的最小值
- max(),返回指定列中数据的最大值
聚集函数的使用规则
- count,min和max可以用于数值和非数值字段,而sum和avg只能用于数值字段
- 除count(*)外,每个函数要先去除控制后计算其非空值。
- 聚集函数只能用在select列表和having子句中
二、group by和having的使用
1.group by的使用
在带有group by子句的查询语句中,在select列表中指定的列要么是group by字句中指定的列,要么包含聚组函数。
以牛客网的SQL33题为例:
题目:现在运营想要找到每个学校gpa最低的同学来做调研,请你取出每个学校的最低gpa
表名:user_profile
id | device_id | university | gpa |
1 | 111 | 北京大学 | 3.4 |
2 | 222 | 复旦大学 | 4 |
3 | 333 | 北京大学 | 3.2 |
4 | 444 | 浙江大学 | 3.6 |
5 | 555 | 山东大学 | 3.8 |
6 | 666 | 山东大学 | 3.3 |
7 | 777 | 复旦大学 | 3.6 |
根据示例,你的查询结果应参考以下格式,输出结果按university升序排序
device_id | university | gpa |
333 | 北京大学 | 3.2000 |
777 | 复旦大学 | 3.6000 |
666 | 山东大学 | 3.3000 |
444 | 浙江大学 | 3.6000 |
错误代码示范:
select device_id,university,min(gpa)
from user_profile
group by university
错误原因在于,group by后跟随的是university字段,则在select子句列表中除了university外,其他都应该使用聚集函数
正确解法之一:
select device_id,university,gpa
from user_profile
where (university,gpa) in (select university,min(gpa) from user_profile group by university)
order by university
此种方法中,第二个select语句的子句列表中只包含university和聚集函数,因此不会报错,在查询到GPA最小值及学校后,再次进行查询即可增加device_id列。
本道题目属于分组排序类问题,也可用窗口函数法解决,窗口函数是面试的重点,大家也需要非常熟练。
2.having的使用
区分where和having
- where子句将单个行过滤到查询结果中,而having子句将分组过滤到查询结果中
- having子句中使用的列名必须出现在group by子句列表中,或包括在聚集函数中。
- having子句的条件运算至少包括一个聚集函数,否则可以把查询条件移到where字句中来过滤单个行(注意聚集函数不可以用在where子句中)
总结
本文简单介绍了什么是聚集函数以及聚集函数的使用规则,并且以牛客网的SQL33题为例,讲解了group by和having的用法和注意事项。