爱站程序员基地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
