leetcode-178-Rank-Scores

发表于   |  

描述

Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no “holes” between ranks.

1
2
3
4
5
6
7
8
9
10
+----+-------+
| Id | Score |
+----+-------+
| 1  | 3.50  |
| 2  | 3.65  |
| 3  | 4.00  |
| 4  | 3.85  |
| 5  | 4.00  |
| 6  | 3.65  |
+----+-------+

For example, given the above Scores table, your query should generate the following report (order by highest score):

1
2
3
4
5
6
7
8
9
10
+-------+------+
| Score | Rank |
+-------+------+
| 4.00  | 1    |
| 4.00  | 1    |
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.50  | 4    |
+-------+------+

分析

比较典型的一道题，生产环境中有时也会有这样的需求。

解决方案(MySQL)

比较容易想到的一个方法：

1
2
3
4
5
# Write your MySQL query statement below
SELECT
Score, (SELECT COUNT(DISTINCT Score) FROM Scores WHERE Score >= s.Score) as Rank
FROM Scores s
ORDER BY Score DESC

在评论区有位朋友给出了这道题的四种写法，倒是可以帮助熟悉 MySQL：

  • 用变量
    一个变量 @rank 表示当前的 rank 值，另一个变量 @prev 表示上一个 Score 数
1
2
3
4
5
6
# Write your MySQL query statement below
SELECT
Score,
@rank := @rank + (@prev <> (@prev := Score)) AS Rank
FROM Scores, (SELECT @rank := 0, @prev := -1) init
ORDER BY Score DESC
  • 每一次都记排名

这个方式跟我上面写的是一种思路，这种性能比较一般

  • 每一次都记排名，但先将 Score 的唯一值记为一张临时表

思路和上一种方式是一样的，但是效率是最好的。原因在于我们将 Scores 中 Score 的唯一值存为了一张临时的表，这样一来，就不需要每次调用 DISTINCT 时创建临时表了，因此效率更高。

1
2
3
4
5
6
7
# Write your MySQL query statement below

SELECT
Score,
(SELECT COUNT(*) FROM (SELECT DISTINCT Score as s FROM Scores) as tmp WHERE s >= Score) AS Rank
FROM Scores
ORDER BY Score DESC
  • 利用 Score 的比较将两张表进行自然连接
    有意思的是，这种方法超时了。。。。提交了好几次也没有 「Accepted」
1
2
3
4
5
6
7
# Write your MySQL query statement below

SELECT 
s.Score, COUNT(DISTINCT t.Score) as Rank
FROM Scores s JOIN Scores t ON s.Score <= t.Score
GROUP BY s.Id
ORDER BY s.Score DESC

题目来源