三种得出序列号的用法与区别

row_number()、dense_rank()、rank() 三种排序的用法和区别 : 

    --row_number() 是没有重复值的排序(即使两条记录相等也是不重复的)，可以利用它来实现分页
    --dense_rank() 是连续排序
    --rank()       是跳跃排序

下面开始举例：

首先创建测试数据

create table test(
	name varchar(8),
	score int
)
insert into test(name,score)values('数学',80);
insert into test(name,score)values('英语',80);
insert into test(name,score)values('语文',80);
insert into test(name,score)values('数学',80);
insert into test(name,score)values('数学',86);
insert into test(name,score)values('数学',93);
insert into test(name,score)values('英语',67);
insert into test(name,score)values('英语',67);
insert into test(name,score)values('数学',80);

1.rank()

select name,score,rank() over (partition by name order by score) as xh from test;
select name,score,rank()over(order by score) as xh from test;

2.dense_rank()

select name,score,dense_rank() over (partition by name order by score) as xh from test;
select name,score,dense_rank()over(order by score) as xh from test;

3.row_number()

select name,score,row_number() over (partition by name order by score) as xh from test;
select name,score,row_number()over(order by score) as xh from test;

小结:通过观察运行sql语句和观察查询结果可以明确的区分rank()、dense_rank()、row_number()的区别，以及加了partition by 和不加partition by 的区别。