mysql分组查询 groud by

group by 使用

查询每个年级的学分总和
select grade,sum(score) from student group by grade

group by 使用两个参数

查询每个年级学生不同性别的学分最高
select grade,max(score),sex from student group by grade,sex

group by 配合where方式查询

查询每个年级最高学分并且大于85的数据
注：如果可以使用where方式解决尽量使用where的方式。having的执行效率低于where的执行效率。除非没有办法使用where

having方式：
select grade,max(score) as maxScore from student group by grade having maxScore>85

where方式：
select grade,max(score) as maxScore from student where score>85 group by grade

group by 配合having方式查询

查询每个年级的平均数，平均数大于40的数据
因为where里面没有办法计算平均数 ，这种情况就要用having
select grade,avg(score) as avgScore from student group by grade having avgScore>40

group by 使用where和having方式查询

查询除了三年级以外每个年级平均数，并且平均数大于40，根据性别倒序输出
select grade,avg(score) as avgScore,sex from student where grade != "三年级" group by sex,grade having avgScore>40 order by sex desc