以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 | +-------+------+