在平常的工作中,我们经常需要对数据进行分组,那么group by该如何使用呢?都有哪些使用场景呢?
语法:含有聚合函数的过滤条件需放在having后
1):select 字段 from 表名 where 条件 group by 字段
2):select 字段 from 表名 (where 条件) group by 字段 having 条件
1:使用group by统计男生、女生人数:
select sex,count(sex) from student group by sex;
2:使用case when + group by 进行行转列:
select st.name,st.student_no,
sum(case when sc.course = '语文' then sc.score end) as '语文',
sum(case when sc.course = '数学' then sc.score end) as '数学',
sum(case when sc.course = '英语' then sc.score end) as '英语'
from score sc left join student st on sc.student_no = st.student_no GROUP BY
sc.student_no,st.name
3:统计范围数据:查询各科成绩一般、良好和优秀的人数:
select course,
sum(case when score > 60 and score <=70 then 1 else 0 end) '一般',
sum(case when score > 70 and score <=80 then 1 else 0 end) '良好',
sum(case when score > 80 then 1 else 0 end) '优秀'
from score GROUP BY course;
4:常用的聚合函数:min(),max(),avg(),sum()等:
1):查询每门课程的最高分:max()
select course,max(score) from score GROUP BY course;
2):查询每门课程的最低分:min()
select course,min(score) from score GROUP BY course;
3):查询每门课程的平均分:
select course,avg(score) from score GROUP BY course;
4):查询平均分大于85的课程:
select course from score GROUP BY course having avg(score) > 85;
以上为group by的基本使用场景,不断的学习才会遇见更好的自己!