Leetcode - Mysql ranking questions - 178. Rank Scores

以leetcode上这道题为例：

---

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.

+----+-------+
| 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):

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

---

---

1. row_number() in sql server:

row_number() over([partition by column1] order by column2 desc/asc) as Rank

该排序是从1开始，每行增加1，连续不断。

用Mysql如何实现？

• Mysql可用user defined varible来解决：

SELECT s.Score, 
       @rownum := @rownum + 1 AS Rank
  FROM Scores s, 
       (SELECT @rownum := 0) r
order by s.Score desc;

得到table如下：

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

---

2. rank() in sql server:

1 2 3 4

RANK() OVER (     [PARTITION BY partition_expression, ... ]     ORDER BY sort_expression [ASC | DESC], ... )

• 用MySql join实现：

from Scores s1, Scores s2
where s1.Id != s2.Id
and s1.Score <= s2.Score

得到如下的table：

id1	score1	id2	score2
1	3.5	2	3.65
1	3.5	3	4
1	3.5	4	3.85
1	3.5	5	4
1	3.5	6	3.65
2	3.65	3	4
2	3.65	4	3.85
2	3.65	5	4
2	3.65	6	3.65
3	4	5	4
4	3.85	4	3.85
4	3.85	3	4
4	3.85	5	4
5	4	3	4
6	3.65	2	3.65
6	3.65	3	4
6	3.65	4	3.85
6	3.65	5	4

可以看出，每个id1对应的score2数目就是其排名：

select s1.Score, count(s2.Id) as Rank
from Scores s1, Scores s2
where s1.Id != s2.Id
and s1.Score <= s2.Score
group by s1.Id
order by s1.Score desc

得到table如下：

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

---

3. dense_rank() in sql server:

2 3 4

DENSE_RANK() OVER (     [PARTITION BY partition_expression, ... ]     ORDER BY sort_expression [ASC | DESC], ... )

• 同样可以用MySql的join来解决：

From Scores s1 inner join Scores s2 on s1.Score <= s2.Score

self join之后得到的table如下：

id1	score1	id2	score2
1	3.5	1	3.5
1	3.5	2	3.65
1	3.5	3	4
1	3.5	4	3.85
1	3.5	5	4
1	3.5	6	3.65
2	3.65	2	3.65
2	3.65	3	4
2	3.65	4	3.85
2	3.65	5	4
2	3.65	6	3.65
3	4	3	4
3	4	5	4
4	3.85	4	3.85
4	3.85	3	4
4	3.85	5	4
5	4	5	4
5	4	3	4
6	3.65	2	3.65
6	3.65	3	4
6	3.65	4	3.85
6	3.65	5	4
6	3.65	6	3.65

可以看出，这个时候每个id1对应的score2的unique数就是其排名：

Select s1.Score, count(Distinct s2.Score) as Rank
From Scores s1 inner join Scores s2
on s1.Score <= s2.Score
Group by s1.Id
Order by s1.Score desc;

得到table如下：

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