1.RANK,DENSE_RANK和ROW_NUMBER的区别
-
RANK并列跳跃排名,并列即相同的值,相同的值保留重复名次,遇到下一个不同值时,跳跃到总共的排名。
-
DENSE_RANK并列连续排序,并列即相同的值,相同的值保留重复名次,遇到下一个不同值时,依然按照连续数字排名。
-
ROW_NUMBER连续排名,即使相同的值,依旧按照连续数字进行排名。
1.1 RANK 函数
语法结构
RANK() OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)
实例(1):按照分数字段直接排序,两个并列第一名后,下一个是第三名,会跳过第二名,间断排序
select
c.c_name,
t1.c_id,
t1.s_score,rank() over (ORDER BY t1.s_score desc) as 'rank'
from
Score t1,Course c
where t1.c_id='01'
and t1.c_id=c.c_id
效果:
实例(2):若按照某个字段分区进行排序的话,例如按照name进行分区,根据分数进行排名:
- 首先,PARTITION BY子句按姓名将结果集分成多个分区。
- 然后,ORDER BY子句按分数对结果集进行排序。
select
c.c_name,
t1.c_id,
t1.s_score,rank() over (partition by c.c_name ORDER BY t1.s_score desc) as 'rank'
from
Score t1,Course c
where t1.c_id='01'
and t1.c_id=c.c_id
效果:
1.2 DENSE_RANK 函数
分组连续排名,排名是并列且连续的
语法结构:
DENSE_RANK() OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)
实例:
select
c.c_name,
t1.c_id,
t1.s_score,dense_rank() over (partition by c.c_name ORDER BY t1.s_score desc) as 'rank'
from
Score t1,Course c
where t1.c_id='01'
and t1.c_id=c.c_id
效果:
1.3 ROW_NUMBER 函数
ROW_NUMBER()是一个窗口函数或分析函数,它为从1开始应用的每一行分配一个序号
语法结构:
ROW_NUMBER() OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
实例:
select
c.c_name,
t1.c_id,
t1.s_score,row_number() over (partition by c.c_name ORDER BY c.c_name desc) as 'num'
from
Score t1,Course c
where t1.c_id='01'
and t1.c_id=c.c_id
效果: