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;