mysql查询语句举例

转自:http://www.cnblogs.com/sunada2005/p/3393953.html

学生成绩表(stuscore)：

姓名：name	课程：subject	分数：score	学号：stuid
张三	数学	89	1
张三	语文	80	1
张三	英语	70	1
李四	数学	90	2
李四	语文	70	2
李四	英语	80	2

 

2. 问题：

1. 计算每个人的总成绩并排名，并按总成绩降序排列(要求显示字段：学号，姓名，总成绩)
2. 计算每个人单科的最高成绩(要求显示字段: 学号，姓名，课程，最高成绩)
3. 列出各门课程成绩最好的学生(要求显示字段: 学号，姓名, 科目，成绩)
4. 列出各门课程成绩最好的两位学生(要求显示字段: 学号，姓名,科目，成绩)
5. 列出各门课程的平均成绩，并按平均成绩降序排列（要求显示字段：课程，平均成绩）
6. 列出总分成绩的排名（要求显示字段：学号，姓名，成绩，排名）
7. 列出数学成绩在2-3名的学生（要求显示字段：学号，姓名,科目，成绩）
8. 求出李四的数学成绩的排名
9. 统计如下：

学号	姓名	语文	数学	英语	总分	平均分

 10. 统计如下：

课程	不及格（0-59）个	良（60-80）个	优（81-100）个

  

 

 

 

3. 参考答案

1. select stuid, name, sum(score) as sum_score from stuscore group by stuid order by sum_score desc;
2. select stuid, name, sub, score from stuscore where score in (select max(score) from stuscore group by stuid);
3. select stuid, name, sub, score from stuscore where score in (select max(score) from stuscore group by sub);
4. select a.* from stuscore a where exists (select count(*) from stuscore where sub = a.sub and score > a.score having count(*) < 2) order by a.sub, a.score desc;
5. select sub, avg(score) as avg_score from stuscore group by sub order by avg_score desc;
6. select (select (count(stuid)+1 from (select stuid, sum(score) as sum_score from stuscore group by stuid) as A where A.sum_score > B.sum_score) as seq, B.stuid, B.name, B.sum_score from (select stuid, name, sum(score) as sum_score from stuscore group by stuid) as B order by sum_score desc;
7. select stuid, name, score, sub from stuscore where sub = 'math' order by score desc limit 1, 3;
8. select (select (count(stuid)+1 from (select stuid, score from stuscore where sub = 'math') as A where A.score > B.score) as seq, B.stuid, B.name, B.sum_score from (select stuid, name, sub, score from stuscore where sub = 'math' and name = '李四') as B;
9. select stuid, name, sum(case when sub = 'chinese' then score else 0 end) as chinese, sum(case when sub = 'math' then score else 0 end) as math, sum(case when sub = 'english' then score else 0 end) as english, sum(score) as sum_score, avg(score) as avg_score from stuscore group by stuid;
10. select sub, sum(case when score < 60 then 1 else 0 end) as lower_60, sum(case when score < 81 and score > 59 then 1 else 0 end) as between_60_80, sum(case when score > 80 then 1 else 0 end) as higher_80 from stuscore group by sub;