mysql max(sum())问题
原题目是学生成绩管理系统:查出成绩总和最高的学生的名称及总分数.(MYSQL)
成绩表:
学生表:
我尝试用以下语句查询:
select studentid,studentName,max(sumScoreValue) from
(
select sum(scoreValue) as sumScoreValue, a.studentid,b.studentName
from sc_score a , sc_student b
where a.studentid = b.studentNo group by studentid
)table1
得到的结果 max(sumScoreValue)正确取得最高分数,但是studentid,studentName是默认第一个学生的数值。
无柰之下,我用以下语句进行查询:
select max(sumScoreValue) as sumScoreValue, studentid,studentName from (
select sum(scoreValue) as sumScoreValue, a.studentid,b.studentName
from sc_score a , sc_student b
where a.studentid = b.studentNo group by studentid
)table1 where sumScoreValue = (
select max(scoreValue) from (
select sum(scoreValue) as score