MySQL查询 |
您所在的位置：网站首页 › java计算成绩表的总分和平均分并输出学生信息及结果 › MySQL查询 |
MySQL查询
|
现有学生表（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 |
【本文地址】
今日新闻 |
推荐新闻 |