泛微E9运维常用SQL（预制菜）

1、导出2025年系统所有流程发起过的和归档的数据--导出2025年流程发起过得和归档的 SELECT wt.typename AS 工作流种类, -- 关联种类表获取名称 wb.workflowname AS 工作流名称, SUM(wr.total_create) AS 2025创建数量, SUM(wr.archive_create) AS 2025归档数量 FROM workflow_base wb -- 关联工作流种类表通过workflowtype关联种类ID INNER JOIN workflow_type wt ON wb.workflowtype wt.id -- workflow_base的种类ID关联workflow_type的id -- 关联工作流实例统计结果 INNER JOIN ( SELECT workflowid, COUNT(*) AS total_create, SUM(CASE WHEN currentnodetype 3 THEN 1 ELSE 0 END) AS archive_create FROM workflow_requestbase WHERE createdate 2025-01-01 GROUP BY workflowid ) wr ON wb.id wr.workflowid -- 按种类名称和工作流名称分组 GROUP BY wt.typename, wb.workflowname ORDER BY wt.typename, wb.workflowname;2、将所有流程中文本描述部分包含特定字眼的批量替换用于运维人员变动替换--查询哪些流程包含“张龙”字眼 select distinct c.tablename, d.workflowname, b.descriptivetext from workflow_billfield a, workflow_specialfield b, workflow_bill c, WORKFLOW_BASE d where a.idb.fieldid and c.ida.billid and b.descriptivetext like %张龙% and d.FORMIDc.id --批量将张龙替换成吴红仪 UPDATE workflow_specialfield SET descriptivetext REPLACE(descriptivetext, 张龙, 吴红仪) WHERE descriptivetext LIKE %张龙%;3、查询系统中哪些流程和节点有JS和接口--查询哪些流程有JS select b.id,c.id,c.nodename,b.workflowname from workflow_nodehtmllayout a,workflow_base b,workflow_nodebase c where a.scriptstr is not null and a.workflowidb.id and a.nodeidc.id --查询哪些流程有接口 SELECT a.actionname 动作流名称, a.interfaceid 接口名称,b.workflowname 路径id, c.nodename 节点名称, d.linkname 出口名称,a.ispreoperator 是否节点前附加操作 FROM workflowactionset a LEFT JOIN workflow_base b ON a.workflowidb.id LEFT JOIN workflow_nodebase c ON a.nodeidc.id LEFT JOIN workflow_nodelink d ON a.nodelinkid d.id WHERE a.interfacetype 3 --查询哪些流程有JSE8 select b.id,c.id,c.nodename,b.workflowname from workflow_nodehtmllayout a,workflow_base b,workflow_nodebase c where a.scriptstr is not null and a.workflowidb.id and a.nodeidc.id4、删除流程中意见区内容--查询流程的流转意见 select * from workflow_requestlog where requestid8362775 -- 将目标记录的 remark 字段置空条件需要按照以上查找出来得节点id做操作 UPDATE workflow_requestlog SET remark NULL -- 明确指定置空 WHERE requestid8362775 AND nodeid500521;4、查询部门矩阵被哪些流程引用以及流程节点/*查询部门矩阵被哪些流程引用以及流程节点*/ select a.workflowname 流程名称,a.version 版本号,c.nodename 节点名称,d.groupname 操作组名称, g.name 矩阵名称 from workflow_base a inner join workflow_flownode b on a.idb.workflowid and b.NODETYPE0 --不等于创建节点 inner join workflow_nodebase c on b.nodeid c.id --and c.IsFreeNode is null inner join workflow_nodegroup d on c.idd.nodeid inner join workflow_groupdetail e on d.ide.groupid inner join workflow_groupdetail_matrix f on f.groupdetailid e.id inner join matrixinfo g on g.id f.matrix where e.type99 --矩阵类型 and a.isvalid 1 --有效流程 and f.matrix in (2) --矩阵id右上角 order by a.workflowname,c.nodename,d.groupname5、部门矩阵的具体哪几个角色被哪些流程用直接上级被哪些流程引用SELECT id AS 矩阵ID, name AS 矩阵名称 FROM MatrixInfo WHERE name 部门矩阵; SELECT id FROM MatrixFieldInfo WHERE matrixid 2 AND displayname 人事基础栏目人员晋升降职流程用; SELECT (SELECT workflowname FROM workflow_base WHERE id c.workflowid) AS 流程名称, (SELECT nodename FROM workflow_nodebase WHERE id c.nodeid) AS 节点名称 FROM workflow_flownode c WHERE c.nodeid IN ( SELECT nodeid FROM WORKFLOW_NODEGROUP WHERE id IN ( SELECT groupid FROM WORKFLOW_GROUPDETAIL WHERE id IN ( SELECT groupdetailid FROM WORKFLOW_GROUPDETAIL_MATRIX WHERE matrix 2 AND value_field 111--分别是部门ID和角色id ) ) ) AND c.workflowid IN (SELECT id FROM workflow_base WHERE isvalid ! 0); -- 过滤无效流程 select distinct t2.workflowname, t3.nodename from (select workflowid, nodeid from workflow_flownode where nodeid in (select nodeid from workflow_nodegroup where id in (select groupid from workflow_groupdetail where type 18))) t1 left join workflow_base t2 on t1.workflowid t2.id left join workflow_nodebase t3 on t1.nodeid t3.id --workflow_groupdetail type 其中18是直接上级99是部门矩阵6、需求单流程快速更改时间和业务类型--查询流程编号的id流程编号预计交付测试时间预计上线时间测试所需天数业务类型 SELECT requestid, name1,name36,name37,cssxts,YWLX FROM formtable_main_209 WHERE name1流程编号 --修改业务类型0是需求类1是运维类 UPDATE formtable_main_209 SET YWLX1 WHERE name1IT004202510278636 --修改时间和测试所需天数 UPDATE formtable_main_209 SET name362026-xx-xx,name372026-xx-xx ,cssxtsX WHERE name1XX7、查询修改管理员密码--查询人员信息表 select * from hrmresourcemanager --修改系统管理员密码 update hrmresourcemanager set passwordC4CA4238A0B923820DCC509A6F75849B where id1 and lastname系统管理员8、特殊时期处理数据常用的表--E9要删这些表 delete from workflow_requestbase where requestid? delete from workflow_form where requestid? delete from workflow_formdetail where requestid? delete from workflow_requestlog where requestid? delete from workflow_currentoperator where requestid? delete from workflow_nownode where requestid? delete from workflow_requestoperatelog where requestid? delete from workflow_requestexception where requestid? delete from workflow_requestflowinfo where requestid? delete from workflow_requestflowlog where requestid? delete from workflow_requestselectnextflow where requestid? delete from Workflow_SubwfRequest where subrequestid? delete from workflow_approvelog where requestid? delete from workflow_approveerrorlog where requestid? delete from workflow_track where requestid? delete from workflow_trackdetail where requestid? --E8要删这些表 delete FROM workflow_requestbase WHERE requestid IN delete FROM workflow_form WHERE requestid IN delete FROM workflow_formdetail WHERE requestid IN delete FROM workflow_requestlog WHERE requestid IN delete FROM workflow_currentoperator WHERE requestid IN delete FROM workflow_nownode WHERE requestid IN delete FROM workflow_requestoperatelog WHERE requestid IN delete FROM workflow_requestexception WHERE requestid IN delete FROM workflow_approvelog WHERE requestid IN delete FROM workflow_approveerrorlog WHERE requestid IN delete FROM workflow_track WHERE requestid IN delete FROM workflow_trackdetail WHERE requestid IN9、查询部门及其下属部门所发起的流程--以部门id为40021为例 select t1.requestid,t1.requestname,t3.DEPARTMENTNAME as 部门,t2.LASTNAME as 创建人,t1.CREATEDATE as 创建日期,t1.CREATETIME as 创建时间,t4.WORKFLOWNAME 路径名称 from workflow_requestbase t1,hrmresource t2,(select id,DEPARTMENTNAME from hrmdepartment start with id40021 connect by prior idsupdepid) t3,workflow_base t4 where t1.CREATER t2.ID and t1.WORKFLOWID t4.id and t2.DEPARTMENTID t3.id10、查询自定义报表导出操作SELECT hrm.lastname AS 操作人姓名, ex.userid AS 操作人ID, ex.name AS 报表名称, ex.status AS 导出状态, ex.exportpercent AS 导出进度, ex.startdate AS 开始日期, ex.starttime AS 开始时间, ex.enddate AS 结束日期, ex.endtime AS 结束时间, ex.taskType AS 任务类型 FROM workflow_exportreporttask ex LEFT JOIN hrmresource hrm ON ex.userid hrm.id ORDER BY ex.id DESC;11、导出云南惠铜分部下从2025年开始创建过什么流程E8select distinct d.typename 流程类型, b.workflowname 流程名称 from workflow_requestbase a, workflow_base b, hrmresource c, workflow_type d where a.workflowidb.id and a.creater c.id and c.subcompanyid1133 and ---分部id a.createdate2025-01-01 and ---时间 d.idb.workflowtype order by d.typename,b.workflowname12、查询某个人在一个时间内签核的流程相关信息select b.workflowname 流程名称, c.requestname 流程标题, d.nodename 节点名称, a.operatedate 签核日期, e.preisremark, f.lastname 创建人, f.workcode 创建人工号, h.dept_name 创建部门 from workflow_requestlog a, workflow_base b, workflow_requestbase c, workflow_nodebase d, workflow_currentoperator e, hrmresource f, BM_PJ_V h where a.operator ? and a.operatedate2024-01-01 and a.workflowidb.id and a.requestidc.requestid and e.requestida.requestid and e.isremark in(1,2,8,9) and e.userid ? and f.idc.creater and h.dept_idf.departmentid and d.ida.nodeid ORDER BY b.workflowname,a.operatedate;13、查询某个人的所有待办E8select distinct a.requestid, d.workflowname 流程名称, c.requestname 流程标题, c.createdate 创建日期, a.receivedate 接收日期, e.lastname 创建人, e.workcode 工号, b.nodename 当前节点, a.isremark from workflow_currentoperator a, workflow_nodebase b, workflow_requestbase c, workflow_base d, hrmresource e where a.userid 30226 and a.isremark in(0,1,8,9) and a.requestidc.requestid and c.currentnodeidb.id and c.workflowidd.id and e.idc.creater