现有学生表(student)与成绩表(grade)
1.查询所有学生的数学成绩,显示学生姓名name, 分数, 由高到低
SELECT student.name,grade.score
FROM student,grade
WHERE student.id = grade.id AND kemu = '数学'
ORDER BY score DESC
2.统计每个学生的总成绩,显示字段:姓名,总成绩
SELECT student.name,sum(grade.score) as sum_score
FROM student,grade
WHERE student.id = grade.id
GROUP BY name
3.统计每个学生的总成绩(由于学生可能有重复名字),显示字段:学生id,姓名,总成绩
SELECT a.id,a.name,c.sum_score
FROM student a,
(SELECT grade.id,sum(grade.score) as sum_score
FROM grade
GROUP BY id) c
WHERE a.id = c.id
ORDER BY sum_score DESC
4.列出各门课程成绩最好的学生, 要求显示字段: 学号,姓名,科目,成绩
select a.id, a.name, b.kemu, b.score
from student a,grade b,
(select grade.kemu,max(grade.score) as maxscore
from grade
group by kemu) c
where b.kemu=c.kemu and b.score=c.maxscore and b.id=a.id
5.列出各门课程成绩最好的2位学生, 要求显示字段: 学号,姓名,科目,成绩
select g1.id,s.name,g1.kemu,g1.score
from grade g1,student s
where (select count(*)
from grade g2
where g1.kemu=g2.kemu and g1.score<g2.score)<2 and g1.id=s.id
order by g1.kemu desc
6.计算每个人的平均成绩, 要求显示字段: 学号,姓名,平均成绩
select s.id,s.name,a.avgscore
from student s,
(select g.id,avg(g.score) as avgscore
from grade g
group by g.id) a
where s.id=a.id
7.列出各门课程的平均成绩,要求显示字段:课程,平均成绩
select g.kemu,avg(g.score) as avgscore
from grade as g
group by kemu
8.列出数学成绩前3名的学生(要求显示字段:学号,姓名, 科目,成绩)
select s.id,s.name,g.kemu,g.score
from student s,grade g
where kemu="数学" and s.id=g.id
group by score desc
limit 3
9.统计英语课程少于80分的,显示 学号id, 姓名,科目,分数
select s.id,s.name,g.kemu,g.score
from student s,grade g
where g.kemu="英语" and g.score<80 and s.id=g.id
group by 用法总结
函数 | 作用 |
sum(列明) | 求和 |
max(列明) | 最大值 |
min(列明) | 最小值 |
avg(列明) | 平均值 |
count(列明) | 统计记录数 |