Oracle的ROW_NUMBER() OVER()函数

2020-03-19  

ROW_NUMBER() OVER() 函数的作用:分组排序

 

原理:
row_number() over() 函数,over() 里的分组以及排序的执行晚于 where、group by、order by 的执行。

 

语法:
row_number() over( partition by 分组列 order by 排序列 desc )


实践:
建立学生数据表

create table st 
(
id int,
name varchar2(100),
classid int,
score int
);

insert into st values(1, '学生1', 1, 88);
insert into st values(2, '学生2', 3, 68);
insert into st values(3, '学生3', 1, 78);
insert into st values(4, '学生4', 2, 87);
insert into st values(5, '学生5', 1, 89);
insert into st values(6, '学生6', 2, 91);
insert into st values(7, '学生7', 3, 67);
insert into st values(8, '学生8', 1, 77);
insert into st values(9, '学生9', 3, 77);
commit;

 

1、查学生数据,根据分数排名

select id, name, classid, score, row_number() over(order by score desc) rank  from st;

 

2、查学生数据根据班级分组,再根据分数排名。获取到每个班级的学生分数排名

select id, name, classid, score, row_number() over(partition by classid order by score desc) rank from st;

 

3、获取到每个班级分数排名第一的学生

select * from (select id, name, classid, score, row_number() over(partition by classid order by score desc) rank from st) where rank = 1;

 

有点几点需要说明:

  • parttion by 是 Oracle 中分析性函数的一部分,用于给结果集进行分区,它和聚合函数 group by 不同的地方在于它只是将原始数据进行名次排列,能够返回一个分组中的多条记录(记录数不变),而 group by 是对原始数据进行聚合统计,一般只有一条反映统计值的结果(每组返回一条)。
  • over() 必须有 ORDER BY 语句
  • 分组内从 1 开始排序
  • over() 中的排序字段为空,会被排到第一

将学生1的分数设置为 null,再获取到每个班级的学生分数排名

select id, name, classid, score, row_number() over(partition by classid order by score desc) rank from st;

可以这么修改,修正这个问题

select id, name, classid, score, row_number() over(partition by classid order by score desc nulls last) rank from st;

 

ConstXiong 备案号:苏ICP备16009629号-3