oracle: ROW_NUMBER() OVER(PARTITION BY) 用法

select rank() over(partition by c_sec_code order by D_ADJ desc) rk,
                        b.*
                   from T_D_SV_FI_PAY b
                  where b.N_CHECK_STATE = 1
                    and B.D_ADJ between date'2018-08-07'-400
                    and date'2018-08-07'
                    and getday_sec(B.C_SEC_CODE, B.D_ADJ - 1, 1, 'W') < date'2018-08-07'

create table test(
  c_Sec_Code varchar2(50),
  d_sett date,
  d_exr date,
  d_sett_fact date,
  n_sett_money_s number,
  c_td_no varchar2(50),
  c_port_code varchar2(20)
);


--- 插入9条语句
insert into test (C_SEC_CODE, D_SETT, D_EXR, D_SETT_FACT, N_SETT_MONEY_S, C_TD_NO, C_PORT_CODE)
values ('00939 HS', to_date('09-08-2018', 'dd-mm-yyyy'), to_date('09-07-2018', 'dd-mm-yyyy'), to_date('09-08-2018', 'dd-mm-yyyy'), 1455000, '2018071001', 'Z00225');

insert into test (C_SEC_CODE, D_SETT, D_EXR, D_SETT_FACT, N_SETT_MONEY_S, C_TD_NO, C_PORT_CODE)
values ('00939 HS', to_date('09-08-2018', 'dd-mm-yyyy'), to_date('22-06-2017', 'dd-mm-yyyy'), to_date('09-08-2018', 'dd-mm-yyyy'), 1455000, '2018071001', 'Z00225');

insert into test (C_SEC_CODE, D_SETT, D_EXR, D_SETT_FACT, N_SETT_MONEY_S, C_TD_NO, C_PORT_CODE)
values ('00939 HS', to_date('09-08-2018', 'dd-mm-yyyy'), to_date('22-06-2017', 'dd-mm-yyyy'), to_date('09-08-2018', 'dd-mm-yyyy'), 1455000, '2018071001', 'Z00225');

insert into test (C_SEC_CODE, D_SETT, D_EXR, D_SETT_FACT, N_SETT_MONEY_S, C_TD_NO, C_PORT_CODE)
values ('01398 HS', to_date('09-08-2018', 'dd-mm-yyyy'), to_date('05-07-2018', 'dd-mm-yyyy'), to_date('09-08-2018', 'dd-mm-yyyy'), 1444800, '2018070601', 'Z00225');

insert into test (C_SEC_CODE, D_SETT, D_EXR, D_SETT_FACT, N_SETT_MONEY_S, C_TD_NO, C_PORT_CODE)
values ('01398 HS', to_date('09-08-2018', 'dd-mm-yyyy'), to_date('03-07-2017', 'dd-mm-yyyy'), to_date('09-08-2018', 'dd-mm-yyyy'), 1444800, '2018070601', 'Z00225');

insert into test (C_SEC_CODE, D_SETT, D_EXR, D_SETT_FACT, N_SETT_MONEY_S, C_TD_NO, C_PORT_CODE)
values ('01398 HS', to_date('09-08-2018', 'dd-mm-yyyy'), to_date('03-07-2017', 'dd-mm-yyyy'), to_date('09-08-2018', 'dd-mm-yyyy'), 1444800, '2018070601', 'Z00225');

insert into test (C_SEC_CODE, D_SETT, D_EXR, D_SETT_FACT, N_SETT_MONEY_S, C_TD_NO, C_PORT_CODE)
values ('01398 HS', to_date('09-08-2018', 'dd-mm-yyyy'), to_date('05-07-2018', 'dd-mm-yyyy'), to_date('09-08-2018', 'dd-mm-yyyy'), 60200, '2018070601', 'Z00261');

insert into test (C_SEC_CODE, D_SETT, D_EXR, D_SETT_FACT, N_SETT_MONEY_S, C_TD_NO, C_PORT_CODE)
values ('01398 HS', to_date('09-08-2018', 'dd-mm-yyyy'), to_date('03-07-2017', 'dd-mm-yyyy'), to_date('09-08-2018', 'dd-mm-yyyy'), 60200, '2018070601', 'Z00261');

insert into test (C_SEC_CODE, D_SETT, D_EXR, D_SETT_FACT, N_SETT_MONEY_S, C_TD_NO, C_PORT_CODE)
values ('01398 HS', to_date('09-08-2018', 'dd-mm-yyyy'), to_date('03-07-2017', 'dd-mm-yyyy'), to_date('09-08-2018', 'dd-mm-yyyy'), 60200, '2018070601', 'Z00261');

   1. partition by c_sec_code,c_port_code 根据c_sec_code,c_port_code分类.

    2. row_number() over() 对分类好的数据排序, 并且虚拟出一个排序的列.
select ROW_NUMBER() OVER(PARTITION BY C_SEC_CODE,c_port_code ORDER BY A.D_EXR DESC) RN,
 a.* from test a