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