mysql 查询出成绩表排名，两种实现方式

​

数据表，来自leetcode

Create table If Not Exists Scores (Id int, Score DECIMAL(3,2))

Truncate table Scores

insert into Scores (Id, Score) values ('1', '3.5')

insert into Scores (Id, Score) values ('2', '3.65')

insert into Scores (Id, Score) values ('3', '4.0')

insert into Scores (Id, Score) values ('4', '3.85')

insert into Scores (Id, Score) values ('5', '4.0')

insert into Scores (Id, Score) values ('6', '3.65')

方式一：

SELECT Score,

CASE
      WHEN @fs = Score THEN @pm
      WHEN @fs := Score THEN @pm := @pm + 1
      END Rank           

FROM Scores1,

(SELECT @pm := 0,@fs := null) a           

ORDER BY Score DESC;

方式二：

SELECT a.Score, SUM(CASE WHEN b.Score >= a.Score THEN 1 END)

FROM Scores1 a,

(SELECT DISTINCT Score FROM Scores1) b           

GROUP BY a.id,a.Score

ORDER BY a.Score DESC;