2021-07-31

SELECT DISTINCT
a.FK_FLOW_ID,
a.submitid submitId,
a.mettingid mettingId,
a.PRJMEMBER,
b.projectName,
c.name prjTypeName,
a.functionary,
d.name deptName,
f.username submitName,
m.METTINGCONCLUSION prjSetupFinalResult,
a.id id,
i.createdate applyDate,
fd.name fdname,
mm.mettingstartdate,
mm.PROGRAMCOMMITTEEID meetingMem1
FROM
hc_prj_flow_base a left join METTING_MANAGEMENT mm ON a.METTINGID = mm.id LEFT JOIN prj_project b ON a.fk_prj_id = b.id LEFT JOIN prj_types c ON b.fk_typesid = c.id LEFT JOIN forp_dept d ON d.id = a.DEPTNAMEID left join forp_dept fd ON fd.NODENO = d.PARENTNODENO LEFT JOIN (
SELECT
fk_instanceid,
operateuser,
createdate
FROM
(
SELECT
fk_instanceid,
operateuser,
createdate,
row_number () over ( partition BY fk_instanceid ORDER BY id DESC ) rn
FROM
flow_instance_node
WHERE
nodename = ‘申请人发起’
) a
WHERE
rn = 1
) i ON A.fk_flow_id = i.fk_instanceid LEFT JOIN ( SELECT fi.fk_instanceid instanceId, wmsys.wm_concat ( DISTINCT fi.operateuser ) operateuserids FROM FLOW_INSTANCE_NODE fi GROUP BY fi.fk_instanceid ) fiu ON i.fk_instanceid = fiu.instanceId LEFT JOIN forp_user f ON f.id = i.operateuser LEFT JOIN metting_management m ON m.id = a.mettingId
WHERE
a.flowtype = 0