SQL实现LeetCode(178.分数排行)

[LeetCode] 178.Rank Scores 分数排行

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 Score, (SELECT COUNT(DISTINCT Score) FROM Scores WHERE Score >= s.Score) Rank FROM Scores s ORDER BY Score DESC;

解法二：

SELECT Score,(SELECT COUNT(*) FROM (SELECT DISTINCT Score s FROM Scores) t WHERE s >= Score) RankFROM Scores ORDER BY Score DESC;

解法三：

SELECT s.Score, COUNT(DISTINCT t.Score) RankFROM Scores s JOIN Scores t ON s.Score <= t.ScoreGROUP BY s.Id ORDER BY s.Score DESC;

解法四：

SELECT Score,@rank := @rank + (@pre <> (@pre := Score)) RankFROM Scores, (SELECT @rank := 0, @pre := -1) INIT ORDER BY Score DESC;

https://leetcode.com/discuss/40116/simple-short-fast

[LeetCode] 178.Rank Scores 分数排行

更多相关文章

随机推荐