mysql group 关键字_MySQL汇总分析（group by）

制作表格的代码在这里找哦！https://zhuanlan.zhihu.com/p/162696033​zhuanlan.zhihu.comstu表：学生学号、学生姓名、学生年龄、学生性别

sc表：学生学号、课程编号、成绩co表：课程编号、课程名称和教师编号te表：教师编号和姓名

MySQL使用group by关键字对数据进行分组,

通常和集合函数一起使用。

答案及讲解

#1.查询每门课程被选修的学生数 :

SELECT c_id,COUNT(*)学生数 FROM sc GROUP BY c_id;

#2.查询每位同学的平均成绩，并按分数降序、学生编号升序排列：

order by排序，默认是升序排列 ASC可以省略。

SELECT s_id,AVG(score)平均成绩 FROM sc GROUP BY s_id ORDER BY score DESC,s_id ASC;

#3.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列：

SELECT c_id,AVG(score)平均成绩 FROM sc GROUP BY c_id ORDER BY 平均成绩 DESC,c_id ASC;

#4.查询90后学生的男女数量，并按照数量降序排列：

SELECT s_sex,count(*)数量 FROM stu WHERE YEAR(s_age)>='1990' GROUP BY s_sex ORDER BY 数量 DESC;

#扩展，想要横向显示怎么办？

横竖方向互换一般会用sum 或count 加上case ……when 或者if函数，具体怎么做请看https://zhuanlan.zhihu.com/p/160859939​zhuanlan.zhihu.com

SELECT SUM(s_sex='男')男生数量,SUM(s_sex='女')女生数量 FROM stu WHERE YEAR(s_age)>='1990';

#5.查询不同性别、各年份的出生人数：

#聚合函数 多变一 #分组聚合 多变少

SELECT s_sex,YEAR(s_age)年份,count(*)人数 from stu GROUP BY s_sex,年份;

#查找重复值

-- group by可以和having一起限定显示记录所需满足的条件，只有满足条件的分组才会被显示

#6.查询同名同姓学生名单，并统计有同名的学生姓名和同名人数：

SELECT s_name,count(s_name)人数 FROM stu GROUP BY s_name HAVING 人数>1;

#含义：查找名字，人数 从stu表里通过s_name分组 分组后各组人数大于1(大于1才有重复)

#7.查询每门课程的平均成绩，并且平均成绩大于等于70分：

SELECT c_id,AVG(score)平均成绩 FROM sc GROUP BY c_id HAVING 平均成绩>=70;

#8.查询至少选修了2门课的学生学号：

SELECT s_id,COUNT(c_id)课程数 FROM sc GROUP BY s_id HAVING 课程数>=2;

#9.查询至少两门课程及格的学生学号：

SELECT s_id,count(*)课程数 FROM sc WHERE score>=60 GROUP BY s_id HAVING 课程数>=2;

select s_id from sc group by s_id having sum(score>=60)>=2;

# sum(score>=60)=sum(if(score>=60,1,0))各组大于60分的课程大于2

#10.查询学过编号为“01”并且也学过编号为“02”的课程的学生编号：

SELECT s_id FROM sc WHERE c_id in ('01','02') GROUP BY s_id HAVING count(c_id)>=2;

select s_id from sc group by s_id having sum(c_id="01")>0 and sum(c_id="02")>0;

#根据学号分组后每一学号课程编号1和2都不为0，则说明两门课程都学过

#11.查询学过编号为“01”但是没有学过编号为“02”的课程的学生编号：

select s_id

from sc

group by s_id

having sum(c_id="01")=1 and sum(c_id="02")=0;

还有一种思路：把学过02号课程的学生都排除，再找学过01课程的学生结合两个条件，就是我们想要的结果

SELECT DISTINCT s_id FROM sc WHERE c_id='02';

所有学过02课程的学生学号

SELECT s_id FROM sc

WHERE c_id ='01' and

s_id not in (SELECT DISTINCT s_id FROM sc WHERE c_id='02')

GROUP BY s_id ;

#12.查询仅学过编号为“01”并且也学过编号为“02”的课程的学生编号：

select s_id

from sc

group by s_id

having sum(c_id="01") and sum(c_id="02") and count(*)=2;

#13.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，最高分，最低分，平均分，及格率；

select c_id,max(score) 最高分,min(score) 最低分,avg(score) 平均分,

SUM(score>=60)/count(*)及格率

from sc

group by c_id;

亲们可以按照这个题目练习一下：

题目：

1.查询每门课程被选修的学生数 :

2.查询每位同学的平均成绩，并按分数降序、学生编号升序排列：

3.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列：

4.查询90后学生的男女数量，并按照数量降序排列：

扩展，想要横向显示怎么办？

5.查询不同性别、各年份的出生人数：

6.查询同名同姓学生名单，并统计有同名的学生姓名和同名人数：

7.查询每门课程的平均成绩，并且平均成绩大于等于70分：

8.查询至少选修了2门课的学生学号：

9.查询至少两门课程及格的学生学号：

10.查询学过编号为“01”并且也学过编号为“02”的课程的学生编号：

11.查询学过编号为“01”但是没有学过编号为“02”的课程的学生编号：

12.查询仅学过编号为“01”并且也学过编号为“02”的课程的学生编号：

13.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，最高分，最低分，平均分，及格率