ORACLE 中row_number() over（partition by)详解

row_number 语法
ROW_NUMBER()函数将针对SELECT语句返回的每一行，从1开始编号，赋予其连续的编号。在查询时应用了一个排序标准后，只有通过编号才能够保证其顺序是一致的，当使用ROW_NUMBER函数时，也需要专门一列用于预先排序以便于进行编号

partition by关键字是分析性函数的一部分，它和聚合函数不同的地方在于它能返回一个分组中的多条记录，而聚合函数一般只有一条反映统计值的记录，partition by用于给结果集分组，如果没有指定那么它把整个结果集作为一个分组，分区函数一般与排名函数一起使用。

原始表: cux_score 
s_id 表是学生编号，c_id表是课程编号，s_score 表是学生对应的课程分数

  create table cux_score(
    s_id  number,
    c_id  number,
    s_score number
  );

 

根据现在的数据导出需要插入的语句

 select 'insert into cux_score(s_id,c_id,s_score) values(' || s_id || ',' || c_id || ',' ||
             s_score || ')' soruce
        from cux_score;

 

导入测试数据

insert into cux_score (s_id, c_id, s_score) values (1, 1, 80);
insert into cux_score (s_id, c_id, s_score) values (1, 2, 90);
insert into cux_score (s_id, c_id, s_score) values (1, 3, 99);
insert into cux_score (s_id, c_id, s_score) values (2, 1, 70);
insert into cux_score (s_id, c_id, s_score) values (2, 2, 60);
insert into cux_score (s_id, c_id, s_score) values (2, 3, 80);
insert into cux_score (s_id, c_id, s_score) values (3, 1, 80);
insert into cux_score (s_id, c_id, s_score) values (3, 2, 80);
insert into cux_score (s_id, c_id, s_score) values (3, 3, 80);
insert into cux_score (s_id, c_id, s_score) values (4, 1, 50);
insert into cux_score (s_id, c_id, s_score) values (4, 2, 30);
insert into cux_score (s_id, c_id, s_score) values (4, 3, 20);
insert into cux_score (s_id, c_id, s_score) values (5, 1, 76);
insert into cux_score (s_id, c_id, s_score) values (5, 2, 87);
insert into cux_score (s_id, c_id, s_score) values (6, 1, 31);
insert into cux_score (s_id, c_id, s_score) values (6, 3, 34);
insert into cux_score (s_id, c_id, s_score) values (7, 2, 89);
insert into cux_score (s_id, c_id, s_score) values (7, 3, 98);

 

1.要求:得出每门课程的学生成绩排序（升序）

  因为是每门课程的结果，并且要排序，所以用 row_number  (不影响总的记录数据依然是18条)

 select cse.*, row_number() over(partition by c_id order by s_score) from cux_score cse;

 

 

2:进一步要求:得出每门课程的学生成绩,并且按照70分作为分割线排序—即低于70分的排序，高于70分的排序

  select cse.*,
           row_number() over(partition by c_id,(case
             when s_score > 70 then
              1
             else
              0
           end) order by s_score)
      from cux_score cse;