[SQL学习] 分析函数 row_number

- ROW_NUMBER函数的语法描述
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions137.htm#SQLRF06100
ROW_NUMBER( )
   OVER ([ query_partition_clause ] order_by_clause)

- ROW_NUMBER函数在分组排序统计上有着自己的应用场合
- 有关分析函数的扩展可以参考Oracle的官方文档中的“Analytic Functions”描述：http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#SQLRF06174

- 通过实验看一下ROW_NUMBER函数的使用方法
-- 创建测试表T，并初始化几条数据
create table t (group_id number(10), name varchar2(10), salary int);
insert into t values (1,'Tom',1200);
insert into t values (2,'Kary',2400);
insert into t values (2,'Joe',800);
insert into t values (3,'Erick',3600);
insert into t values (3,'Andy',600);
insert into t values (3,'Mary',300);
commit;

-- T表全貌
-- 共三组数据，group_id分别是1、2和3。第1组有一个人，第2组有两个人，第3组有三个人。最后一列是每个人的薪水值
select * from t;

  GROUP_ID NAME                               SALARY
---------- ------------------------------ ----------
         1 Tom                                  1200
         2 Kary                                 2400
         2 Joe                                   800
         3 Erick                                3600
         3 Andy                                  600
         3 Mary                                  300

6 rows selected.

-- ROW_NUMBER函数应用之一：按照每组人的薪酬的高低进行排序
-- 在数据的最后一列我们添加了RN列，这一列可以得到每一组中人员薪酬从低到高的序号
select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary) as rn from t;

  GROUP_ID NAME                               SALARY         RN
---------- ------------------------------ ---------- ----------
         1 Tom                                  1200          1
         2 Joe                                   800          1
         2 Kary                                 2400          2
         3 Mary                                  300          1
         3 Andy                                  600          2
         3 Erick                                3600          3

6 rows selected.

-- ROW_NUMBER函数应用之二：通过ROW_NUMBER计算得到的结果便可进一步得到每组薪酬最高的两位员工（注意这里没有考虑到薪酬相同的情况）
-- 需要嵌套SQL
--- 错误用法
--- 注意RN是不能直接在where子句中使用的
select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary desc) as rn from t where rn<=2;
select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary desc) as rn from t where rn<=2
                                                                                                                 *
ERROR at line 1:
ORA-00904: "RN": invalid identifier

--- 正确用法
select * from (select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary desc) as rn from t ) where rn <= 2;

  GROUP_ID NAME                               SALARY         RN
---------- ------------------------------ ---------- ----------
         1 Tom                                  1200          1
         2 Kary                                 2400          1
         2 Joe                                   800          2
         3 Erick                                3600          1
         3 Andy                                  600          2

select group_id,name from (select group_id, name, salary, row_number() over (PARTITION BY group_id ORDER BY salary desc) as rn from t ) where rn <= 2;

  GROUP_ID NAME
---------- ------------------------------
         1 Tom
         2 Kary
         2 Joe
         3 Erick
         3 Andy