oracle的ROW_NUMBER() OVER函数的基本用法

 转自：http://www.cnblogs.com/icebutterfly/archive/2009/08/05/1539657.html

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

 

row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)

 

例子1：row_number中有1,2,3等数字

SElECT row_number() over(partition by b.agency_item_id, c.processinst_id order by c.createdate desc) row_num,
                    b.agency_item_id,
                    b.agency_id,
                    d.*
               FROM tb_bgt_dept_status b
               join tb_bgt_ref_dept_wf c
                 on b.id = c.bgt_wf_id
               join v_wf_working d
                 on c.processinst_id = d.PROCINSTID
              where b.annual = 2016

注意：例1的图见下面

 

 

 

例子2：都是数字1

SElECT row_number() over(partition by b.agency_item_id, c.processinst_id order by c.createdate desc) row_num,
                    b.agency_item_id,
                    b.agency_id,
                    d.*
               FROM tb_bgt_dept_status b
               join tb_bgt_ref_dept_wf c
                 on b.id = c.bgt_wf_id
               join v_wf_working d
                 on c.processinst_id = d.PROCINSTID
              where b.annual = 2016

)where row_num=1

注意：例子2见图2

 

 

第二：查询是否出现一次，查询出现几次

    select count(agency_id) ,t.agency_id  from tb_plan_info t group by t.agency_id

 

 

---------------------------------------------------------------------------------------以下是两个sql对比，效果是一样的。

流程中找到当前实例的当前最大环节的两种做法（查看流程图）

sql1：如下：按照流程实例和操作流程用户进行分组。找到该流程实例中现在操作的最后一个流程

  SELECT VW.*
 FROM V_WF_WORKDONE VW,
 (SELECT PROCINSTID, USERID, MAX(TASK_END_TIME) MAXENDDATE
  FROM V_WF_WORKDONE
  GROUP BY PROCINSTID, USERID) VT
  WHERE VW.TASK_END_TIME = VT.MAXENDDATE
  AND VW.PROCINSTID = VT.PROCINSTID
  AND VW.USERID = VT.USERID

 

 

sql2：如下：先按照流程实例和用户进行分组，然后在按照时间排序，最后取第一条数据

SELECT row_number() over(partition by v.PROCINSTID, v.USERID order by v.TASK_END_TIME desc) row_num,
       v.TASKINSTID,
       v.USERID,
       v.PROCINSTID,
       v.taskdef,
       v.TASK_END_TIME
  FROM v_wf_workdone v where row_num=1