mysql查询练习题：用到了group by,case when
Part 1
学生表
成绩表
1.查询所有学生的数学成绩，显示学生姓名name, 分数， 由高到低
$select s.name,g.score from student,grade where s.id=g.id and kemu=math;
2.统计每个学生的总成绩,显示字段：姓名，总成绩
$select s.name,sum(score) from student s,grade g where s.id=g.id group by name;
3.统计每个学生的总成绩（由于学生可能有重复名字）,显示字段：学生id，姓名，总成绩
$select s.id,s,name,sum(g.score) from student s,grade g where s.id=g.id group by id;
4.列出各门课程成绩最好的学生， 要求显示字段: 学号，姓名,科目，成绩
SELECT c.id , a.name, c.kemu, c.score FROM grade c, student a, (SELECT b.kemu, MAX(b.score) as max_score FROM grade b GROUP BY kemu) t WHERE c.kemu = t.kemu AND c.score = t.max_score AND a.id = c.id
Part02
学生表
成绩表
5.计算学生平均分数:计算每个人的平均成绩， 要求显示字段: 学号，姓名，平均成绩
$select s.id,s.name,avg(g.score) from student s ,grade g where s.id=g.id group by s.id;
6.统计各科目成绩:计算每个人的成绩，总分数，平均分，要求显示：学号，姓名，语文，数学，英语，总分，平均分
#这里涉及的新知识为：case when以及sum(case when)，延伸包含count (case when)
使用case when 语法把科目字段分解成具体的科目：语文，数学， 英语
不加sum(case when)：统计的还是每一行记录
$select s.id as 学号,s.name as 姓名,(case when g.kemu='语文' then score else 0 end) as 语文,(case when g.kemu='数学' then score else 0 end) as 数学,(case when g.kemu='英语' then score else 0 end) as 英语 from student s,grade g where s.id=g.id;
加sum (case when)，对应的要加group by按照人员分组，然后统计每一门的成绩
$select s.id as 学号,s.name as 姓名,sum(case when g.kemu='语文' then score else 0 end) as 语文,sum(case when g.kemu='数学' then score else 0 end) as 数学,sum(case when g.kemu='英语' then score else 0 end) as 英语 from student s,grade g where s.id=g.id group by s.id;
tip :如果成绩表中有相同的两条记录时，比如一个学生考了语文两次的成绩，那么sum (case when)统计一个学生各个课程的成绩就会有问题了,如下图
整条查询要求对应的sql语句:
$SELECT a.id as 学号, a.name as 姓名,
sum(case when b.kemu='语文' then score else 0 end) as 语文,
sum(case when b.kemu='数学' then score else 0 end) as 数学,
sum(case when b.kemu='英语' then score else 0 end) as 英语,
sum(b.score) as 总分 , avg(b.score) as 平均分
FROM student a, grade b
where a.id = b.id GROUP BY b.id, b.id
7.每门课程平均成绩:列出各门课程的平均成绩，要求显示字段：课程，平均成绩
$select kemu,avg(score) from grade group by kemu;
8.成绩排名:列出数学成绩的排名， 要求显示字段：学号，姓名，成绩，排名
#在统计排名时，用到一个自增变量的写法，注意！
select s.id,s.name,g.score,@paiming :[email protected] +1 from student s,grade g,(select @paiming:=0) r where s.id=g.id and g.kemu='数学' order by g.score desc ;
这里最后需要感谢我的数据库同学在我整理学习过程中对我的帮助~，唐撒阿米达