oracle 分批分组

语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

 

简单的说row_number()从1开始，为每一条分组记录返回一个数字，这里的ROW_NUMBER() OVER (ORDER BY xlh DESC) 是先把xlh列降序，再为降序以后的没条xlh记录返回一个序号。

需求：根据部门分组，显示每个部门的工资等级

预期结果：

empid       deptid      salary                                  rank
----------- ----------- --------------------------------------- --------------------
1           10          5500.00                                 1
2           10          4500.00                                 2
4           20          4800.00                                 1
3           20          1900.00                                 2
7           40          44500.00                               1
6           40          14500.00                               2
5           40          6500.00                                 3
9           50          7500.00                                 1
8           50          6500.00                                 2

 

 

 

案例：结合

 

 

select  c_line_sn,d_arrive_time,d_leave_time ,ROW_NUMBER() over(partition by c_line_sn order by d_arrive_time) ranks,
lag(d_leave_time,1) over (partition by  c_line_sn order by d_arrive_time)

  from task_record 

where d_arrive_time>=to_date('2013-01-02 01:02:03','YYYY-MM-DD HH:Mi:ss');

 

 

 

 

--------获取当前日期判断

select   trunc(sysdate,'dd') from dual