1、查询数学成绩排名
select ss.stid, ss.name, ss.score, (select count(*) from stuscore s where s.subject='数学' and s.score > ss.score)+1 mark
from stuscore ss where subject='数学' order by ss.score asc;
灵活点:用一个子查询查询出比当前人成绩大的有多少人,那么有多少人就是第几名
2、查询各门课程成绩最好的学生
select t1.stid, t1.name, t1.subject, t1.score from stuscore t1,(select subject, MAX(score) as maxscore from stuscore group by subject) t2
where t1.subject = t2.`subject` and t1.score = t2.maxscore
灵活点:先根据课程分组查询出每门课程最高的成绩和课程名,再根据课程名对比谁的成绩和最高成绩相等,就是课程成绩最好的