Leetcode: Rank Scores

最新推荐文章于 2021-01-14 16:47:25 发布

ojshilu

最新推荐文章于 2021-01-14 16:47:25 发布

阅读量1k

点赞数

CC 4.0 BY-SA版权

分类专栏： LeetCode 数据库

本文链接：https://blog.csdn.net/ojshilu/article/details/44424887

LeetCode 同时被 2 个专栏收录

151 篇文章

订阅专栏

数据库

7 篇文章

订阅专栏

本文介绍如何使用SQL编写查询语句来计算分数的动态排名，并优化查询效率。通过实例展示如何处理并解决分数相同时的排名问题，确保排名连续且无空隙。此外，介绍了使用子查询、自定义变量等方法来提高查询性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

Answer:

Get one Score from a, and Get the Rank from b.

The rank is the count of distinct value that larger then a.Score.

select a.Score, count(distinct(b.Score))
from Scores as a, Scores as b
where b.Score >= a.Score
group by b.Id
order by a.Score desc;

It is a correlated sub-query. Time Complexity is O(N^2). This method runs out of time.

Build a subTable and Put “distinct” in it.

SELECT Scores.Score, COUNT(Ranking.Score) AS RANK
  FROM Scores
     , (
       SELECT DISTINCT Score
         FROM Scores
       ) Ranking
 WHERE Scores.Score <= Ranking.Score
 GROUP BY Scores.Id
 ORDER BY Scores.Score DESC;

User-Defined Variables. So cool!

Initiate one variable in a select statement.
Then update the variable in another select statement.
Then build the format that required.

SELECT Score, Rank 
FROM
(
  SELECT    Score,
            @curRank := @curRank + IF(@prevScore = Score, 0, 1) AS Rank,
            @prevScore := Score
  FROM      Scores s, (SELECT @curRank := 0) c, (SELECT @prevScore := NULL) p
  ORDER BY  Score DESC
) t;