题目描述

在某笔试题中遇到了这样的题目，之前学过数据库原理，但是这综合性太强，一下子犯了难。

解决过程

在数据库中建立上述表，以验证写的SQL对不对

平台：Navicate SQL 16 for MySQL

尝试写SQL查询

尝试1

第一次我直观写出来的SQL是这样的：

select Student_Id,Subject_Id,max(Score) from score group by Subject_Id;

运行一下：

发现并不正确，Subject_Id和max(Score)的关系是正确的，Student_Id的关系是错误的，都被匹配成了Id为1的学生。

原因：

如果要使用group by子句，那么在select指定的字段
要么就要包含在Group By语句的后面，作为分组的依据
要么就要被包含在聚合函数中

这里的Student_Id既没有包含在Group By语句的后面，作为分组的依据，又没有被包含在聚合函数中，所以得到的信息是错误的。

尝试2

由于上面SQL运行出来的Student_Id的关系是错误的，于是把它在SQL中去掉再运行一遍看看

select Subject_Id,max(Score) from score group by Subject_Id;

这样正确找出了各门科目的最高分。

进一步往下思考，怎么得到相应的学生信息？
可以试试把刚刚查出的表和原Score表做一个连接
利用“科目-最高成绩”表，在Score成绩记录表中，找出各门成绩分数≥该门成绩最高分的学生Id，科目Id，分数。

select score.Student_Id,score.Subject_Id,score.Score 
from score,(select Subject_Id,max(Score) Score from score group by Subject_Id) as score_new
where score.Subject_Id = score_new.Subject_Id and score.Score >= score_new.Score;

结果如下：

发现结果是对的。
发现最后的答案 要的是科目和学生名，而不是Id，这时候我们只需要在查询中连接一下科目表和学生表即可。

select subject.Subject_Name,student.Student_Name,score.Score
from student,subject,score,(select Subject_Id,max(Score) Score from score group by Subject_Id) as score_new
where student.Id=score.Student_Id and subject.Id=score.Subject_Id and score.Subject_Id = score_new.Subject_Id and score.Score >= score_new.Score;

查出来的结果为：

差不多大功告成了，还有一个问题就是，还没有将结果按照科目Id排序，再后面加一个order by字段试试。

select subject.Subject_Name,student.Student_Name,score.Score
from student,subject,score,(select Subject_Id,max(Score) Score from score group by Subject_Id) as score_new
where student.Id=score.Student_Id and subject.Id=score.Subject_Id and score.Subject_Id = score_new.Subject_Id and score.Score >= score_new.Score
order by subject.Id;

大功告成！

SQL答案

select subject.Subject_Name,student.Student_Name,score.Score
from student,subject,score,(select Subject_Id,max(Score) Score from score group by Subject_Id) as score_new
where student.Id=score.Student_Id and subject.Id=score.Subject_Id and score.Subject_Id = score_new.Subject_Id and score.Score >= score_new.Score
order by subject.Id;