第二弹：泛微OA常用一些查询SQL分享来啦，文末有彩蛋

SELECT DISTINCT A.WORKFLOWNAME,C.NODENAME,G.LASTNAME,G.STATUS,G.WORKCODE,G.IDFROM workflow_base AS A INNER JOIN workflow_flownode AS B ON B.WORKFLOWID = A.IDINNER JOIN workflow_nodebase AS C ON B.NODEID=C.ID INNER JOIN workflow_nodegroup AS D ON C.ID=D.NODEID INNER JOIN workflow_groupdetail AS E ON D.ID=E.GROUPID INNER JOIN workflow_hrmoperator AS F ON E.GROUPID=F.GROUPID INNER JOIN hrmresource AS G ON F.OBJID=G.ID WHERE G.STATUS='5' ORDER BY A.WORKFLOWNAME

SELECT  E.TYPENAME AS 流程类型,  A.ID 流程ID,  A.WORKFLOWNAME AS 流程名称,  B.GROUPNAME AS 流程节点,  C.CONDITIONCN AS 出口条件,  D.CONDITIONCN AS 批次条件FROM  WORKFLOW_BASE A,  WORKFLOW_NODEGROUP B,  WORKFLOW_NODELINK C,  WORKFLOW_GROUPDETAIL D,  WORKFLOW_TYPE EWHERE A.ID=C.WORKFLOWID  AND C.NODEID=B.NODEID  AND D.GROUPID=B.ID  AND A.WORKFLOWTYPE=E.ID  AND (C.CONDITIONCN IS NOT NULL   OR D.CONDITIONCN IS NOT NULL )

WITH RECURSIVE department_tree (id, DEPARTMENTMARK, supdepid, depth, path) AS (      -- 初始化查询(非递归部分)      SELECT             id,             DEPARTMENTMARK,             supdepid,            1 AS depth,            CAST(id AS CHAR(200)) AS path        FROM hrmdepartment        WHERE canceled IS NULL OR canceled <> 1      UNION ALL      -- 递归查询部分      SELECT             D.id,            D.DEPARTMENTMARK,             D.supdepid,            dt.depth + 1 AS depth,             CONCAT(dt.path, ',', D.id) AS path        FROM hrmdepartment D         JOIN department_tree dt ON D.supdepid = dt.id        WHERE D.canceled IS NULL OR D.canceled <> 1  ),  ranked_departments AS (      SELECT             *,            ROW_NUMBER() OVER (PARTITION BY id ORDER BY depth DESC) AS rn        FROM department_tree    )  -- 选择每个id对应depth最大的记录，并应用字段选择逻辑  SELECT         id,          DEPARTMENTMARK,          supdepid,  depth,  path,    SUBSTRING_INDEX(SUBSTRING_INDEX(path, ',', 1), ',', -1) AS field1,          IF(LENGTH(path) - LENGTH(REPLACE(path, ',', '')) >= 1, SUBSTRING_INDEX(SUBSTRING_INDEX(path, ',', 2), ',', -1), NULL) AS field2,        IF(LENGTH(path) - LENGTH(REPLACE(path, ',', '')) >= 2, SUBSTRING_INDEX(SUBSTRING_INDEX(path, ',', 3), ',', -1), NULL) AS field3,        IF(LENGTH(path) - LENGTH(REPLACE(path, ',', '')) >= 3, SUBSTRING_INDEX(SUBSTRING_INDEX(path, ',', 4), ',', -1), NULL) AS field4,        IF(LENGTH(path) - LENGTH(REPLACE(path, ',', '')) >= 4, SUBSTRING_INDEX(SUBSTRING_INDEX(path, ',', 5), ',', -1), NULL) AS field5,        IF(LENGTH(path) - LENGTH(REPLACE(path, ',', '')) >= 5, SUBSTRING_INDEX(SUBSTRING_INDEX(path, ',', 6), ',', -1), NULL) AS field6    FROM ranked_departments    WHERE rn = 1  

-- 查询不匹配的人员 SELECT H.id, H.LASTNAME, H.DEPARTMENTIDFROM cus_fielddata c  INNER JOIN hrmresource H ON H.id = c.idLEFT JOIN view_bmcjpath v ON H.departmentid = v.id WHERE H.STATUS < 5  AND c.scopeid = -1  AND (    (c.field26 IS NOT NULL AND c.field26 <> v.field1) OR    (c.field26 IS NULL AND v.field1 IS NOT NULL) OR    (c.field27 IS NOT NULL AND c.field27 <> v.field2) OR    (c.field27 IS NULL AND v.field2 IS NOT NULL) OR    (c.field28 IS NOT NULL AND c.field28 <> v.field3) OR    (c.field28 IS NULL AND v.field3 IS NOT NULL) OR    (c.field31 IS NOT NULL AND c.field31 <> v.field4) OR    (c.field31 IS NULL AND v.field4 IS NOT NULL) OR    (c.field32 IS NOT NULL AND c.field32 <> v.field5) OR    (c.field32 IS NULL AND v.field5 IS NOT NULL) OR    (c.field33 IS NOT NULL AND c.field33 <> v.field6) OR    (c.field33 IS NULL AND v.field6 IS NOT NULL)  )-- 更新各级部门不匹配的人员UPDATE cus_fielddata c  INNER JOIN hrmresource a ON a.id = c.id  LEFT JOIN view_bmcjpath v ON a.departmentid = v.id  SET      c.field26 = v.field1,      c.field27 = v.field2,      c.field28 = v.field3,      c.field31 = v.field4,      c.field32 = v.field5,      c.field33 = v.field6  WHERE      c.scopeid = -1      AND a.STATUS < 5      AND (        (c.field26 IS NOT NULL AND c.field26 <> v.field1) OR      (c.field26 IS NULL AND v.field1 IS NOT NULL) OR      (c.field27 IS NOT NULL AND c.field27 <> v.field2) OR      (c.field27 IS NULL AND v.field2 IS NOT NULL) OR      (c.field28 IS NOT NULL AND c.field28 <> v.field3) OR      (c.field28 IS NULL AND v.field3 IS NOT NULL) OR      (c.field31 IS NOT NULL AND c.field31 <> v.field4) OR      (c.field31 IS NULL AND v.field4 IS NOT NULL) OR      (c.field32 IS NOT NULL AND c.field32 <> v.field5) OR      (c.field32 IS NULL AND v.field5 IS NOT NULL) OR      (c.field33 IS NOT NULL AND c.field33 <> v.field6) OR      (c.field33 IS NULL AND v.field6 IS NOT NULL)    )