1：先截图看效果
2：完整的SQl语句
1 SELECT * FROM studentscore; 2 -- ------------------ 3 SET @maxscore=(SELECT MAX(score) FROM studentscore LIMIT 1); 4 SELECT MAX(A.score)score FROM studentscore A 5 INNER join studentscore B ON A.id=B.id 6 AND A.score!=@maxscore LIMIT 1; 7 8 -- ---------------- 9 SELECT 10 S.id,S.score, 11 @myrow:=@myrow+1 AS myrow 12 FROM 13 (SELECT id,score 14 FROM studentscore 15 ORDER BY score DESC) S,(SELECT @myrow:=0)r ; 16 17 -- ------------------ 18 SELECT 19 S.id,S.score, 20 (CASE WHEN 21 @sameScore=S.score THEN @myrow WHEN @sameScore:=S.score THEN @myrow:=@myrow+1 END) AS myrow 22 FROM 23 (SELECT id,score 24 FROM studentscore 25 ORDER BY score DESC) S,( 26 SELECT @myrow:=0,@sameScore:= NULL)r ; 27 -- --------可以求N条---------- 28 SELECT * FROM( 29 SELECT 30 S.id,S.score, 31 (CASE WHEN 32 @sameScore=S.score THEN @myrow WHEN @sameScore:=S.score THEN @myrow:=@myrow+1 END) AS myrow 33 FROM 34 (SELECT id,score 35 FROM studentscore 36 ORDER BY score DESC) S,( 37 SELECT @myrow:=0,@sameScore:= NULL)r 38 ) qq 39 WHERE myrow=2 -- 可以取 @N高的信息，具体看需求 40 41 42 43 44 45 46 47 48 49 50