【LeetCode-SQL】178. 分数排名

一、题目

编写一个 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    |
+-------+------+

重要提示：对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 Rank

二、解决

1、dense_rank()

思路：

create table scores
(   id number(6)
   ,score number(4,2)
);
insert into scores values(1,3.50);
insert into scores values(2,3.65);
insert into scores values(3,4.00);
insert into scores values(4,3.85);
insert into scores values(5,4.00);
insert into scores values(6,3.65);
commit;

select
    id 
   ,score
   ,rank() over(order by score desc) rank               --按照成绩排名，纯排名
   ,dense_rank() over(order by score desc) dense_rank   --按照成绩排名，相同成绩排名一致
   ,row_number() over(order by score desc) row_number   --按照成绩依次排名
   ,ntile(3) over (order by score desc) ntile         --按照分数划分成绩梯队
from scores;

代码：

/* Write your PL/SQL query statement below */
select score as "Score",dense_rank() over (order by score desc) as "Rank" from scores;

2、变量

思路：
第一部分是降序排列的分数，第二部分是每个分数对应的排名。

1、第一部分：降序分数
select a.Score as Score
from Scores a
order by a.Score DESC

2、算出排名Rank
select b.Score from Scores b where b.Score >= X;
-->去重
select count(distinct b.Score) from Scores b where b.Score >= X as Rank;

3、合起来
select a.Score as Score,
(select count(distinct b.Score) from Scores b where b.Score >= a.Score) as Rank
from Scores a
order by a.Score DESC

代码-版本1.1：

SELECT 
    score
    , t1.rank
FROM(
    SELECT score
        , @rnk := if(@pre = score, @rnk, @rnk+1) + 0 as 'rank'
        , @pre := score
    FROM Scores, (SELECT @pre:=null, @rnk:=0) init
    order by score desc
) as t1;

代码-版本1.2：

select t.score,t.rank from scores join
(
    SELECT p.score score, @rank:=@rank+1 'rank' FROM 
        (SELECT distinct(score) score FROM Scores ORDER BY score DESC) p , (SELECT @rank:=0 ) q
) t 
on scores.score = t.score
order by score desc;

MySQL变量类型为何是字符型，发生了自动转换吗，内在的机制是什么？
这里不是自动转换，而是声明的变量默认为字符型。可以通过隐式或显式转换的方式解决变量输出结果不是整数类型的问题，具体可以参考下面代码。

# MySQL
SELECT 
    @r
    , @p
    , @r+0
    , CAST(@r AS UNSIGNED) 
    , CONVERT(@r, SIGNED)
FROM (SELECT @r:=55 ,  @p:=0+1) init

# Output
{"headers": ["@r", "@p", "@r+0", "CAST(@r AS UNSIGNED)", "CONVERT(@r, SIGNED)"], 
"values": [[
	"55", 
	"1", 
	55.0, 
	55, 
	55
]]}

3、表关联

思路： 略。
代码：

SELECT t1.Score, COUNT(DISTINCT t2.Score) AS `RANK`
FROM Scores t1, Scores t2
WHERE t1.Score <= t2.Score
GROUP BY t1.Id
ORDER BY `RANK`;
# ORDER BY t1.Score desc;

三、参考

1、⭐️ LeetCode解题系列 ⭐️ 178. 分数排名（Oracle dense_rank函数）
2、分成两个部分写会容易很多
3、MySQL CONVERT() Function