SQL四大排名函数

0、数据

result表：

---

一、ROW_NUMBER (连续排名)

特点：对于同值元组，排名序号不同。所有相同的元组各自为组。

select *, ROW_NUMBER() OVER (order by score desc) 'Rank'
from result;

结果：

二、RANK (并列跳跃排名)

特点：对于同值元组，排名序号相同。但所有相同的元组各自为组，即对别的元组来说影响都是多元的。

select *, RANK() OVER (order by score desc) 'Rank'
from result;

结果：

RANK代码实现：

select score, (
   select count(score) + 1 
   from result s2 
   where s2.score > s.score
) 'Rank'
from result s 
order by score desc;

三、DENSE_RANK (并列连续排名)

特点：对于同值元组，排名序号相同。且所有相同的元组算作一组，即对别的元组来说影响都是一元的。

select *, DENSE_RANK() OVER (order by score desc) 'Rank'
from result;

结果：

DENSE_RANK代码实现：

select score, (
	select count(distinct score)
	from result s2
	where s2.score >= s.score
) 'Rank'
from result s
order by score desc;

四、NTILE() (分组排名)

特点：按指定列将所有记录分成指定个数的组，每一组序号相同，但组内元组指定的列可能不同。
相当于按指定列先排序，再按长度截断分组。

select *, NTILE(4) OVER (order by score desc) 'Rank'
from result;

结果：

五、PARTITION BY(组内排序)

(1) 按照course_id分组，并在组内进行RANK排序

select *, RANK() OVER (partition by course_id order by score desc) 'Rank'
from result;

结果：

(2) 按照student_id分组，并在组内进行DENSE_RANK排序

select *, DENSE_RANK() OVER (partition by student_id order by score desc) 'Rank'
from result;

结果：

(3) 利用排序代码实现 按照course_id分组，并在组内进行对分数进行DENSE_RANK排序

-- 分组 DENSE_RANK
select *, (
	select count(distinct score)
	from result s2
	where s2.course_id = s.course_id and s2.score >= s.score
) 'Rank'
from result s
order by s.course_id, s.score desc;

(4) 利用排序代码实现 按照student_id分组，并在组内进行对分数进行RANK排序:

select *, (
	select count(score) + 1
	from result s2
	where s2.student_id = s.student_id and s2.score > s.score
) 'Rank'
from result s
order by s.student_id, s.score desc;