【SQL语句】灵活SQL语句

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

灵活点：先根据课程分组查询出每门课程最高的成绩和课程名，再根据课程名对比谁的成绩和最高成绩相等，就是课程成绩最好的