【泛微E9】批量更新解决人员的各级部门跟实际部门不匹配

-- 查询不匹配的人员 
SELECT H.id, H.LASTNAME, H.DEPARTMENTID
FROM cus_fielddata c  
INNER JOIN hrmresource H ON H.id = c.id
LEFT 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)
    )