LeetCode(数据库):分数排名

Create table If Not Exists Scores (Id int, Score DECIMAL(3,2));

Truncate table Scores;

insert into Scores (Id, Score) values ('', '3.5');

insert into Scores (Id, Score) values ('', '3.65');

insert into Scores (Id, Score) values ('', '4.0');

insert into Scores (Id, Score) values ('', '3.85');

insert into Scores (Id, Score) values ('', '4.0');

insert into Scores (Id, Score) values ('', '3.65');

编写一个 SQL查询 来实现分数排名。如果两个分数相同，则两个分数排名（Rank）应该相同。请注意，平局之后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“漏洞”。

+----+-------+

| Id | Score |

+----+-------+

| 1  | 3.50  |

| 2  | 3.65  |

| 3  | 4.00  |

| 4  | 3.85  |

| 5  | 4.00  |

| 6  | 3.65  |

+----+-------+

例如，根据给定的上述 Scores 表，您的查询应该生成以下报告（按最高分排序）：

+-------+------+

| Score | Rank |

+-------+------+

| 4.00  | 1    |

| 4.00  | 1    |

| 3.85  | 2    |

| 3.65  | 3    |

| 3.65  | 3    |

| 3.50  | 4    |

+-------+------+

第一种解决方法：This one counts, for each score, the number of distinct greater or equal scores.(每条数据循环，求和这个数据相等或者大的个数)

SELECT

  Score,

  (SELECT count(distinct Score) FROM Scores WHERE Score >= s.Score) Rank

FROM Scores s

ORDER BY Score desc

第二种：与前一个相同，但更快，因为我有一个子查询“首先独立”分数。不完全确定为什么它更快，我猜MySQL做tmp了一个临时表，并将它用于每个外部分数。

SELECT

  Score,

  (SELECT count(*) FROM (SELECT distinct Score s FROM Scores) tmp WHERE s >= Score) Rank

FROM Scores

ORDER BY Score desc

第三种：

SELECT s.Score, count(distinct t.score) Rank

FROM Scores s JOIN Scores t ON s.Score <= t.score

GROUP BY s.Id

ORDER BY s.Score desc