表介绍
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