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

1：先截图看效果

 

 

 

2：完整的SQl语句

SELECT * FROM studentscore;
-- ------------------
SET @maxscore=(SELECT MAX(score) FROM studentscore LIMIT 1);
  SELECT  MAX(A.score)score FROM studentscore A
INNER join studentscore B  ON A.id=B.id
 AND A.score!=@maxscore LIMIT 1;
 
-- ----------------
 SELECT 
 S.id,S.score, 
@myrow:=@myrow+1  AS myrow
FROM 
(SELECT id,score
FROM studentscore
ORDER BY score DESC) S,(SELECT @myrow:=0)r ;

-- ------------------
 SELECT 
 S.id,S.score, 
 (CASE WHEN 
 @sameScore=S.score THEN @myrow WHEN @sameScore:=S.score THEN @myrow:=@myrow+1 END) AS myrow
FROM 
(SELECT id,score
FROM studentscore
ORDER BY score DESC) S,(
SELECT @myrow:=0,@sameScore:= NULL)r ;
-- --------可以求N条----------
SELECT * FROM(
SELECT 
 S.id,S.score, 
 (CASE WHEN 
 @sameScore=S.score THEN @myrow WHEN @sameScore:=S.score THEN @myrow:=@myrow+1 END) AS myrow
FROM 
(SELECT id,score
FROM studentscore
ORDER BY score DESC) S,(
SELECT @myrow:=0,@sameScore:= NULL)r
) qq
WHERE myrow=2 -- 可以取 @N高的信息，具体看需求