Skip to content

Instantly share code, notes, and snippets.

@beiweiqiang
Created November 30, 2019 09:44
Show Gist options
  • Save beiweiqiang/ef5e5a3fb75ed8e5f513dc8d0cafbc55 to your computer and use it in GitHub Desktop.
Save beiweiqiang/ef5e5a3fb75ed8e5f513dc8d0cafbc55 to your computer and use it in GitHub Desktop.
LeetCode 解题思路 178.分数排名
# 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment