oracle分页排序查询,Oracle案例 分页查询和分组排序

表介绍

KM_REVIEW_MAIN：流程主表

字段DOC_SUBJECT，DOC_CREATE_TIME(流程创建时间)，DOC_CREATOR_ID(即SUBMITTER)，FD_DEPARTMENT_ID(提交人部门)，DOC_STATUS，

DOC_STATUS状态对应：

-- 10 草稿

-- 11 驳回

-- 00 废弃/提交人主动取消

-- 20 待审

-- 30 结束

IT_REQUEST_COMPLETION_DETAIL：过程记录表

每个流程状态发生变化时都会在此表记录一次，关联条件：IT_REQUEST_COMPLETION_DETAIL.FD_PROCESS_ID = KM_REVIEW_MAIN.FD_ID

记录FD_PROCESS_ID对应的FD_CREATE_TIME，FD_STATUS，FD_HANDLER_ID处理人，

每个FD_PROCESS_ID有多个不同的FD_CREATE_TIME，FD_STATUS，取最晚的FD_STATUS，就是最新状态。

正常流程：等待(或驳回)-->已受理-->完成请求-->申请人确认完毕，

要求统计为已受理、等待、问题三个状态，

状态对应：

--- 100 等待

--- 200 已受理

--- 300 申请人确认完毕

--- 404 问题

--- 600 完成请求

---其他 未知

IT_REQUEST_PLAN_TIME计划完成时间表

字段：

FD_PROCESS_ID，即KM_REVIEW_MAIN的FD_ID

FD_CREATE_TIME，本条信息创建时间

FD_PLAN_FINISH_TIME，计划完成时间

KM_REVIEW_MAIN_PROPERTY流程类型表

字段：

FD_DOC_ID，即KM_REVIEW_MAIN的FD_ID

FD_PROPERTY_ID，流程类型，

测试服务器-内部需求：16c55f20f811ce47da6b70e418192f67

测试服务器-外部需求：16c5165a9805ef77b491b074e2a8f63b

SYS_ORG_ELEMENT员工信息表

字段

FD_ID(唯一ID)，FD_NAME(名字)，FD_NO(工号)

SQL语句

SELECT

T.*,

CEIL(TOTAL_NUM / 10 ) TOTAL_PAGE

FROM(

SELECT

S.*,

COUNT(*) OVER() TOTAL_NUM,

ROWNUM RNUM

FROM(

SELECT

M.FD_ID PROCESS_ID,

M.DOC_SUBJECT SUBJECT,

SOE.FD_ID SUBMITTER_ID,

SOE.FD_NAME SUBMITTER_NAME,

CASE P.FD_PROPERTY_ID

WHEN '16c55f20f811ce47da6b70e418192f67' THEN '内部'

WHEN '16c5165a9805ef77b491b074e2a8f63b' THEN '外部'

ELSE '其他'

END AS TYPE,

TO_CHAR(M.DOC_CREATE_TIME,'YYYY-MM-DD') SUBMIT_TIME,

SOE2.FD_ID DEPARTMENT_ID,

SOE2.FD_NAME DEPARTMENT_NAME,

SOE1.FD_ID HANDLER_ID,

SOE1.FD_NAME HANDLER_NAME,

TO_CHAR(PT.FD_PLAN_FINISH_TIME,'YYYY-MM-DD') PLAN_FINISH_TIME,

D.FD_STATUS STATUS_NO,

CASE D.FD_STATUS

WHEN '100' THEN '等待'

WHEN '200' THEN '已受理'

WHEN '404' THEN '问题'

WHEN '600' THEN '已完成'

WHEN '300' THEN '已完成'

ELSE '未知状态码'

END AS STATUS,

D.FD_CREATE_TIME FD_CREATE_TIME,

--依照流程ID对记录进行编号，按照时间、状态排序

ROW_NUMBER() OVER(

PARTITION BY D.FD_PROCESS_ID

ORDER BY D.FD_CREATE_TIME DESC, D.FD_STATUS * 1 DESC

) AS RN

FROM KM_REVIEW_MAIN M

LEFT JOIN SYS_ORG_ELEMENT SOE ON SOE.FD_ID = M.DOC_CREATOR_ID

LEFT JOIN IT_REQUEST_COMPLETION_DETAIL D ON D.FD_PROCESS_ID = M.FD_ID

LEFT JOIN IT_REQUEST_PLAN_TIME PT ON PT.FD_PROCESS_ID = M.FD_ID

LEFT JOIN KM_REVIEW_MAIN_PROPERTY P ON P.FD_DOC_ID = M.FD_ID

LEFT JOIN SYS_ORG_ELEMENT SOE1 ON SOE1.FD_ID = D.FD_HANDLER_ID

LEFT JOIN SYS_ORG_ELEMENT SOE2 ON SOE2.FD_ID = SOE1.FD_PARENTID

WHERE D.FD_STATUS IN ('100','200','404','600','300')

) S

WHERE RN = '1'

) T

WHERE

RNUM > 0*10 AND RNUM < 1*10

--如果查询本周

AND TO_DATE(SUBMIT_TIME,'YYYY-MM-DD') >= TRUNC(SYSDATE,'IW')

AND TO_DATE(SUBMIT_TIME,'YYYY-MM-DD') < TRUNC(SYSDATE+7,'IW')

--如果查询本月

AND SUBSTR(SUBMIT_TIME,1,7) = TO_CHAR(SYSDATE,'YYYY-MM')

--查询开始时间~结束时间

AND TO_DATE(SUBMIT_TIME,'YYYY-MM-DD') >= TO_DATE('2019-08-01','YYYY-MM-DD')

AND TO_DATE(SUBMIT_TIME,'YYYY-MM-DD') < TO_DATE('2019-08-20','YYYY-MM-DD')

AND SUBMITTER_ID = '16437e3e2c29277f392d75c41d29574a'

AND TYPE = '内部'

AND DEPARTMENT_ID = '163e57e1db53402ec4897c04713a400b'

AND STATUS = '已完成';

问题一：分组排序

KM_REVIEW_MAIN 中的一个 FD_ID，对应 IT_REQUEST_COMPLETION_DETAIL 的多条记录，

要求按照 FD_ID 分组，每组 FD_ID 的多条记录再按照时间、编号排序

分组排序方法一

左外连接查询，多层次排序，

先大排序：依照 M.FD_ID，

再小排序：依照 D.FD_CREATE_TIME DESC, D.FD_STATUS * 1 DESC

如果单纯只按 D.FD_CREATE_TIME 排序，可能出现错乱，这条流程的记录跟另一条流程的记录穿插出现，

SELECT

M.FD_ID PROCESS_ID,

D.FD_STATUS STATUS_NO,

D.FD_CREATE_TIME

FROM KM_REVIEW_MAIN M

LEFT JOIN IT_REQUEST_COMPLETION_DETAIL D ON D.FD_PROCESS_ID = M.FD_ID

ORDER BY M.FD_ID, D.FD_CREATE_TIME DESC, D.FD_STATUS * 1 DESC

分组排序方法二

编号同时排序，编号时用 PARTITION BY 分组，用 ORDER BY 排序，

SELECT

M.FD_ID PROCESS_ID,

D.FD_STATUS STATUS_NO,

D.FD_CREATE_TIME,

ROW_NUMBER() OVER(

PARTITION BY D.FD_PROCESS_ID

ORDER BY D.FD_CREATE_TIME DESC, D.FD_STATUS * 1 DESC

) AS RN

FROM KM_REVIEW_MAIN M

LEFT JOIN IT_REQUEST_COMPLETION_DETAIL D ON D.FD_PROCESS_ID = M.FD_ID

问题二：取最新状态码

一个流程对应有多个流程记录，需要最后一个流程记录作为最新状态，

思路：取所有流程记录，并按时间倒序排列后编号，然后在外层查询中取编号为1的记录，

内层查询

ROW_NUMBER() OVER(

PARTITION BY D.FD_PROCESS_ID

ORDER BY D.FD_CREATE_TIME DESC, D.FD_STATUS * 1 DESC

)AS RN

外层查询筛选编号为1的

WHERE RN = '1'

问题三：分页查询

首先在内层查询语句中给每条记录编号，并查询总记录数目

SELECT

S.*,

COUNT(*) OVER() TOTAL_NUM,

ROWNUM RNUM

假设pageSize=10，即每页展示10条记录，那么页数=TOTAL_NUM/10并向上取整，TOTAL_NUM是65，则总页数是7，

外层查询时查出总页数，并把编号进行限定在要展示的页数范围内，

SELECT

T.*,

CEIL(TOTAL_NUM / 10 ) TOTAL_PAGE

FROM(

SELECT

S.*,

COUNT(*) OVER() TOTAL_NUM,

ROWNUM RNUM

FROM (***)S

)T

WHERE

RNUM > 0*10 AND RNUM < 1*10

问题四：查询本周数据

AND TO_DATE(SUBMIT_TIME,'YYYY-MM-DD') >= TRUNC(SYSDATE,'IW')

AND TO_DATE(SUBMIT_TIME,'YYYY-MM-DD') < TRUNC(SYSDATE+7,'IW')

TRUNC(SYSDATE,'IW')是查询本周一的00:00:00，注意周日属于本周最后一天，

TRUNC(SYSDATE+7,'IW')是查询下周一的00:00:00，

问题五：查询本月数据

AND SUBSTR(SUBMIT_TIME,1,7) = TO_CHAR(SYSDATE,'YYYY-MM')

问题六：同一张表的不同条件的字段放在同一行

SELECT

A.*,

B1.字段1,

B2.字段2

FROM

A

LEFT JOIN B AS B1 ON B1.ID = A.ID

LEFT JOIN B AS B2 ON B2.ID = A.FID