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

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
View Code

 

posted @ 2020-08-26 14:03  天天向上518  阅读(260)  评论(0)  编辑  收藏  举报