每日一道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

二、解题思路

看到排名就要想到的三个窗口函数
rank() 间断不连续
dense_rank() 连续
row_number() 行号

1.使用窗口函数

根据题目要求名次是一个连续的整数值，所以使用dense_rank() 即可。
代码如下（示例）：

select
score
,dense_rank() over(order by score desc) as "Rank"
from scores

2.使用子查询

如果想不起来窗口函数还可以使用子查询

思路：根据题意拆分成两部分

1. 降序排列的分数

select 
score
from scores
order by score desc

2. 分数对应的排名
   如何获得分数对应的排名呢？假设分数为S，分数集合为H
   因为排名是连续的，所以S的名次应该是H中大于等于S的分数去重计数的结果（去重后的分数的个数）

# 大于分数S的集合H1
select score from scores where score >= S;
# 对H1去重计数
select count(distinct score) from scores where score >= S;

3. 将1、2结合

#每次从a表中取一个分数都与b表进行比较，b表中大于a的去重的分数的个数就是a的排名
select 
a.score
,(select count(distinct b.score) from scores as b where b.score >= a.score) as "Rank"  
from scores as a
order by a.score desc

3. 使用内部变量+join（不常用）

代码示例：

# 获得每个不同的分数的名次
select 
t1.score
,@rank := @rank + 1 as "rank"
from 
(select distinct score as score from scores order by score desc) t1
(select @rank := 0) t2
# 主表与上述的中间表join求得所有分数的排名
select 
s.score
,t.rank as  "Rank"
from scores as s
inner join (
	select 
	t1.socre
	,@rank := @rank + 1 as "rank"
	from (select distinct score as score from scores order by score desc) as t1
		 (select @rank := 0) as t2
) as t 
on s.score = t.score
order by s.score desc

总结

本题主要考察排序的窗口函数