mysql如何查询每门课的最低分的同学的学号与分数

Question

我写的代码是SELECT c_name, min(grade) from student,score,course WHERE student.id=score.stu_id AND course.c_id=score.cor_id GROUP BY c_name；
但无法查询出每门课的最低分的同学的学号与分数，我的代码如何改进？

Answer 1

大概这样吧。。~

select course.c_name,student.name,t.grade 
from
(
 select t.grade,t.cor_id,score.stu_id 
 from (SELECT min(grade) as grade,cor_id FROM `score` group by cor_id)t,score where t.grade=score.grade and t.cor_id=score.cor_id
)t

left join student on student.id=t.stu_id
left join course on course.c_id=t.cor_id

order by course.c_name