mysql排序的问题与获取第几高的分数的信息

1：先截图看效果

SELECT * FROM studentscore;-- ------------------SET @maxscore=(SELECT MAX(score) FROM studentscore LIMIT 1);SELECT  MAX(A.score)score FROM studentscore AINNER join studentscore B  ON A.id=B.idAND A.score!=@maxscore LIMIT 1;-- ----------------SELECTS.id,S.score,@myrow:=@myrow+1  AS myrowFROM(SELECT id,scoreFROM studentscoreORDER BY score DESC) S,(SELECT @myrow:=0)r ;-- ------------------SELECTS.id,S.score,(CASE WHEN@sameScore=S.score THEN @myrow WHEN @sameScore:=S.score THEN @myrow:=@myrow+1 END) AS myrowFROM(SELECT id,scoreFROM studentscoreORDER BY score DESC) S,(SELECT @myrow:=0,@sameScore:= NULL)r ;-- --------可以求N条----------SELECT * FROM(SELECTS.id,S.score,(CASE WHEN@sameScore=S.score THEN @myrow WHEN @sameScore:=S.score THEN @myrow:=@myrow+1 END) AS myrowFROM(SELECT id,scoreFROM studentscoreORDER BY score DESC) S,(SELECT @myrow:=0,@sameScore:= NULL)r) qqWHERE myrow=2 -- 可以取 @N高的信息，具体看需求

View Code