有一张成绩表,查询课程为03的成绩排在第二,三的学生信息,如图:
不考虑并列分数的sql:
使用临时变量,定义一个自增序列
select score.s_id,score.c_id,score.s_score from
(SELECT score.s_id,score.s_score,score.c_id,@i:=@i+1 as 排名 from score,(SELECT @i:=0)s WHERE score.c_id = '03' ORDER BY s_score DESC) A
LEFT JOIN score on A.s_id=score.s_id and A.s_score=score.s_score where A.排名 BETWEEN 2 and 3
考虑并列分数的sql:
select * from score where
(sele