[SQL]LeetCode178. 分数排名 | Rank Scores
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
➤微信公众号：山青咏芝（shanqingyongzhi）
➤博客园地址：山青咏芝（https://www.cnblogs.com/strengthen/）
➤GitHub地址：https://github.com/strengthen/LeetCode
➤原文地址：https://www.cnblogs.com/strengthen/p/10145554.html
➤如果链接不是山青咏芝的博客园地址，则可能是爬取作者的文章。
➤原文已修改更新！强烈建议点击原文地址阅读！支持作者！支持原创！
★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★
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 | +-------+------+
编写一个 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 | +-------+------+
137ms
1 # Write your MySQL query statement below 2 Select Score, 3 @rank:=@rank + (@pre<>(@pre:=Score)) as Rank 4 From Scores, (Select @rank:=0, @pre:=-1) INIT 5 Order by Score DESC;
139ms
1 # Write your MySQL query statement below 2 3 select f.score Score, f.Rank from 4 (select 5 @rnk:=@rnk + case when @score > s.score then 1 else 0 end as Rank 6 ,@score:= s.score as score 7 from 8 (select score from scores order by score desc) s,(select @score:=0,@rnk:=1) t) f
142ms
1 # Write your MySQL query statement below 2 Select Score, 3 @rank:=@rank + (@pre<>(@pre:=Score)) as Rank 4 From Scores, (Select @rank:=0, @pre:=-2) INIT 5 Order by Score DESC;
143ms
1 # Write your MySQL query statement below 2 SELECT 3 Score, 4 @rank := @rank + (@prev <> (@prev := Score)) Rank 5 FROM 6 Scores, 7 (SELECT @rank := 0, @prev := -1) init 8 ORDER BY Score desc
146ms
1 # Write your MySQL query statement below 2 select Score, CONVERT(Rank, SIGNED) AS Rank from ( 3 select Score, 4 if(Score = @previous_score, @rank := @rank, @rank := @rank +1) as Rank, 5 @previous_score := score 6 from Scores, (select @rank := 0, @previous_score:= null) r 7 order by Score desc)v
552ms
1 # Write your MySQL query statement below 2 3 SELECT s.Score, count(distinct t.score) Rank 4 FROM Scores s JOIN Scores t ON s.Score <= t.score 5 GROUP BY s.Id 6 ORDER BY s.Score desc
553ms
1 # Write your MySQL query statement below 2 SELECT s1.Score, count(distinct s2.Score) as Rank 3 FROM Scores s1 4 join Scores s2 on s1.Score <= S2.Score 5 GROUP BY s1.Id 6 ORDER BY 2
