MySQL查询

2024-07-02 21:55| 来源: 网络整理| 查看: 265

现有学生表（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

【本文地址】

公司简介

联系我们