LeetCode 解题思路 178.分数排名

a.sql

# https://leetcode-cn.com/classic/problems/rank-scores/description/

CREATE TABLE IF NOT EXISTS Scores (
  Id    INT AUTO_INCREMENT PRIMARY KEY ,
  Score DECIMAL(3, 2)
);

INSERT INTO Scores (Score) VALUES (3.5);
INSERT INTO Scores (Score) VALUES (3.65);
INSERT INTO Scores (Score) VALUES (4.0);
INSERT INTO Scores (Score) VALUES (3.85);
INSERT INTO Scores (Score) VALUES (4.0);
INSERT INTO Scores (Score) VALUES (3.65);

# 编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。
# 请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。


SET @a:=0;
select @a:=@a+1 rank, Score from (
  select Score from Scores GROUP BY Score ORDER BY Score DESC
) t;

SET @a:=0;
select Score, (
  select @a:=@a+1 rank from (
    select Score from Scores GROUP BY Score ORDER BY Score DESC
  ) t WHERE s.Score = t.Score
) rank from Scores s ORDER BY s.Score DESC;

# 外联
SELECT s.Score, t.rank from Scores s LEFT JOIN (
  select @row:=@row+1 as rank, Score from (
    select Score from Scores GROUP BY Score ORDER BY Score DESC
  ) b ,(SELECT @row := 0) r
) t on s.Score = t.Score ORDER BY s.Score DESC;