泛微OA流程表单归档与未归档SQL连表查询
归档:SELECT DISTINCT
sub.requestid,
sub.requestname,
sub.nodename,
sub.creater_name,
sub.lastname,
sub.workflowname,
sub.outside_name,
sub.node_type,
sub.start_time,
sub.end_time,
sub.质检类型,
sub.最后操作时间
FROM (
SELECT
a.requestid,
a.requestname,
node.nodename,
CASE WHEN a.creater = 1 THEN '系统管理员' ELSE res.lastname END AS creater_name,
cr.lastname,
base.workflowname,
a.status AS outside_name,
CASE WHEN a.currentnodetype = '0' THEN '创建节点'
WHEN a.currentnodetype = '1' THEN '审批节点'
WHEN a.currentnodetype = '2' THEN '处理节点'
ELSE '其他节点' END AS node_type,
CONCAT(a.createdate, ' ', a.createtime) AS start_time,
CONCAT(times.OPERATEDATE, ' ', times.OPERATETIME) AS end_time,
CASE WHEN mains.zjlx = 7 THEN '入库前OQC质检' ELSE '出库前OQC质检' END AS '质检类型',
MAX(CONCAT(times.OPERATEDATE, ' ', times.OPERATETIME)) OVER (PARTITION BY a.requestid) AS 最后操作时间,
ROW_NUMBER() OVER (PARTITION BY a.requestid ORDER BY times.OPERATEDATE DESC, times.OPERATETIME DESC) AS row_num
FROM
workflow_requestbase a
INNER JOIN workflow_nodebase node ON node.id = a.currentnodeid
LEFT JOIN hrmresource res ON res.id = a.creater
LEFT JOIN workflow_base base ON base.id = a.workflowid
LEFT JOIN (
SELECT
requestid,
r1.lastname,
receivedate,
receivetime
FROM
workflow_currentoperator c1
LEFT JOIN hrmresource r1 ON r1.id = c1.userid
WHERE
c1.id IN (
SELECT
MAX(id)
FROM
workflow_currentoperator
GROUP BY
requestid)) cr ON cr.requestid = a.requestid
LEFT JOIN formtable_main_629 mains ON mains.requestid = a.requestid
LEFT JOIN workflow_requestlog times ON times.requestid = a.requestid
WHERE
a.currentnodetype = '3'
AND mains.zjlx IN (7, 8)
AND times.logtype = '0'
AND ((EXISTS (
SELECT
1
FROM
workflow_base wb
WHERE
a.workflowid = wb.id
AND wb.isvalid IN ('0', '1', '2', '3')
AND wb.id IN (
SELECT
id
FROM
workflow_base workbase,
workflow_versioninfo workversion
WHERE
workbase.id = workversion.wfid
AND wfversionid IN (
SELECT
workflowid
FROM
workflow_monitor_detail
WHERE
infoid = 0
))
)))
AND a.deleted < 1
AND base.workflowname = 'SCM15-质检审核电子流'
AND a.status IS NOT NULL
) AS sub
WHERE sub.row_num = 1
ORDER BY
sub.requestid DESC, sub.end_time DESC
LIMIT 10000
未归档SELECT distinct
a.requestid ,
a.requestname ,
node.nodename,
(
CASE WHEN a.creater = 1 THEN
'系统管理员'
ELSE
res.lastname
END) as creater_name,
cr.lastname ,
base.workflowname ,
a.status as outside_name,
(
CASE WHEN a.currentnodetype = '0' THEN
'创建节点'
WHEN a.currentnodetype = '1' THEN
'审批节点'
WHEN a.currentnodetype = '2' THEN
'处理节点'
ELSE
'其他节点'
END) as node_type,
CASE WHEN mains.zjlx = 7 THEN '入库前OQC质检' ELSE '出库前OQC质检' END AS '质检类型'
FROM
workflow_requestbase a
INNER JOIN workflow_nodebase node ON node.id = a.currentnodeid
LEFT JOIN hrmresource res ON res.id = a.creater
LEFT JOIN workflow_base base ON base.id = a.workflowid
LEFT JOIN (
SELECT
requestid,
r1.lastname,
receivedate,
receivetime
FROM
workflow_currentoperator c1
LEFT JOIN hrmresource r1 ON r1.id = c1.userid
WHERE
c1.id IN (
SELECT
max(id)
FROM
workflow_currentoperator
GROUP BY
requestid)) cr ON cr.requestid = a.requestid
left join formtable_main_629 mains on mains.requestid=a.requestid
WHERE
a.currentnodetype != '3' and mains.zjlx in (7,8)
AND ((EXISTS (
SELECT
1
FROM
workflow_base wb
WHERE
a.workflowid = wb.id
AND wb.isvalid IN ('0', '1', '2', '3')
AND wb.id IN (
SELECT
id
FROM
workflow_base workbase,
workflow_versioninfo workversion
WHERE
workbase.id = workversion.wfid
AND wfversionid IN (
SELECT
workflowid
FROM
workflow_monitor_detail
WHERE
infoid = 0)))))
AND a.deleted < 1
AND base.workflowname = 'SCM15-质检审核电子流'
AND a.status is not null
ORDER BY
a.requestid DESC