首先关于mysql中select语句的顺序:select * from tablename where * group by * having * oder by * limit*。
用表:clh_test_student,如下:
1.查询平均分大于80的学生姓名,
SELECT t.name
FROM clh_test_student t
where avg(t.score)>'80';
运行后发现:
语句报错,“集函数的无效用法”,顺手写出的语句,感觉没什么毛病,下面仔细分析问题所在:
where子句是在查询结果分组前,将不符合where条件的查询记录过滤掉,简单的说就是where子句在group by之前起作用。这里要提到一个mysql语法存在的小问题(语法不严谨),mysql中存在‘’默认聚合‘’的情况,即假设SELECT A,B group by A,对B不进行聚合函数处理,结果中会发现对于A的处理为取第一个A。对于上面的报错我个人是这么理解的:一般情况下(oracle、hive),group by是和聚合函数搭配使用的,如果没有聚合函数,group by中应包含所有的字段。where子句在group by之前过滤,那么也就可以理解为where子句也是在聚合函数之前起作用,上面语句报错点where avg(t.score)>’80’,相当于将聚合函数先处理,后对聚合后的结果进行where过滤,问题显而易见。以上关于此问题的解释,完全是我个人的理解,不能为准。
总结下where子句的使用:在group by之前起作用,where子句中不能含有聚合函数。
2、那么上面的问题用什么可以实现呢,下面就要说到having了
SELECT t.name,avg(t.score)
FROM clh_test_student t
GROUP BY t.name
having avg(t.score)>80;
运行结果:
总结下having子句的使用:对分组后的数据进行过滤,having子句中可以有聚合函数,having子句限制的是组而不是行。
3、查询英语和数学平均分大于85的学生姓名和平均分数
SELECT name,avg(score)
FROM clh_test_student
where `subject` in ('english','math')
group by name
HAVING avg(score)>82;
运行结果:
总结:在较复杂查询条件下,需要用聚合函数来限制查询条件时,应当首先想到用having子句,where子句中不能有聚合函数!