LeetCode刷题笔记（SQL-001）

178. Rank Scores

SQL Schema（表结构）

Create table If Not Exists Scores (Id int, Score DECIMAL(3,2));
Truncate table Scores;
insert into Scores (Id, Score) values ('1', '3.5');
insert into Scores (Id, Score) values ('2', '3.65');
insert into Scores (Id, Score) values ('3', '4.0');
insert into Scores (Id, Score) values ('4', '3.85');
insert into Scores (Id, Score) values ('5', '4.0');
insert into Scores (Id, Score) values ('6', '3.65');

问题描述：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    |
+-------+------+

两种解题方法：

方法一：

select A.Score, count(B.Score) as rank
from Scores as A, (select distinct Score
from Scores) as B
where A.score <= B.Score
group by A.Id
order by A.Score desc;

方法二：

select d.score,c.rownum as rank 
from scores d 
inner join 
(select a.score,@rownum := @rownum +1 as rownum 
    from (select distinct score from scores) a,
    (select @rownum :=0) b order by a.score desc) c on c.score=d.score 
order by rank;