mysql中有student,course,scorse 三张表， 计算每个人单科的最高成绩(学生,课程,成绩)

Question

student字段：sid(pk),sno,realname.....
course字段：cid(pk),coursename,.......
score字段：sid(fk),cid(fk),score

Answer 1

获取每一个科目最高分的学生
select sid,cid,max(score) from score group by cid;
然后分别和学生表、课程表联合查询取出科目名字和学生名字就ok了

写成单条sql：
select * from (select s.sid,s.realname,a.cid,a.mscore from student as s right join (select sid,cid,max(score) as mscore from score group by cid)as a on a.sid = s.sid) as a1 left join ((select c.coursename,c.cid from course as c right join (select sid,cid as mscore from score group by cid)as a on a.cid = c.cid) ) as b1 on a1.cid = b1.cid;