[MySQL][LeetCode][Medium] 178. Rank Scores

心得

題目要求找出成績排名,如果分數相同的話則相同名次,MySQL不像MSSQL有ROW_NUMBER()可以用,只好用個變數來存了。

問題

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
11
12
13
14
15
16
17
18
19
20
21
22

+----+-------+
>
> | 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
11
12
13
14
15
16
17
18
19
20
21
22

+-------+------+
>
> | Score | Rank |
>
> +-------+------+
>
> | 4.00 | 1 |
>
> | 4.00 | 1 |
>
> | 3.85 | 2 |
>
> | 3.65 | 3 |
>
> | 3.65 | 3 |
>
> | 3.50 | 4 |
>
> +-------+------+
>
>

答案

1
2
3
4
5
6
7
8
9
10
11
12

# Write your MySQL query statement below
SELECT a.Score, b.Rank
FROM Scores AS a
JOIN (SELECT (@row_number:[email protected]_number + 1) AS Rank, z.Score
FROM (SELECT x.*
FROM Scores AS x
GROUP BY x.Score
ORDER BY x.Score DESC) AS z
JOIN (SELECT @row_number := 0) AS y) AS b
ON a.Score = b.Score
ORDER BY `b`.`Score` DESC