OA 统计宣传品SQL

蓝凌OA中，人员离职后，部门会置为空，如果用inner join 连接 部门表，历史数据中不包含离职人员的统计信息；用left join 才可以

其中：sys_org_element 组织机构表 

          sys_org_person 人员表；

         sys_org_post_person 人员表???

          km_review_main 流程数据表，其中doc_staus 字段10为草稿、20为待审、00为废弃、11为驳回、30为结束， 

旧SQL

 

SELECT
    b.fd_tjr AS id,
    CONVERT(VARCHAR(10),h.doc_create_time,112) as createtime,
    i.fd_login_name AS usercode,
    c.fd_name AS xm,
    k.fd_name as post,
    b.fd_shr AS shr, 
    CONVERT(nvarchar(100), CAST(b.fd_shmobile AS decimal(38,0))) AS mobile, 
    b.fd_xxshdz AS shdz, f.fd_name AS zqmc, 
    d .fd_name AS bm, b.fd_rq AS rq, 
    a.fd_ypmc as wlbh,a.fd_ypmc_text AS wlmc,
    a.fd_cklx as cklx, a.fd_dj AS dj, 
    a.fd_sqsl AS sl
FROM
    ekp_tgj_lrxcpsq_mx a, ekp_tgj_lrxcpsq b, sys_org_element c, sys_org_element d, km_review_main e, sys_org_element f,km_review_main h,sys_org_person i, sys_org_post_person j, sys_org_element k
WHERE
    a.fd_parent_id = b.fd_id 
    AND b.fd_tjr = c.fd_id 
    AND b.fd_tjrbm = d .fd_id 
    AND a.fd_sqsl IS NOT NULL 
    AND e.fd_id = b.fd_id 
    AND e.doc_status IN ('20','30','31') 
    AND a.fd_ypmc_text IS NOT NULL 
    AND d .fd_parentid = f.fd_id 
    AND b.fd_rq > '202009' 
    AND b.fd_id = h.fd_id
    AND b.fd_tjr = i.fd_id
    AND i.fd_id = j.fd_personid
    AND j.fd_postid = k.fd_id;

新

SELECT
    b.fd_tjr AS id,
    CONVERT(VARCHAR(10),h.doc_create_time,112) as createtime,
    i.fd_login_name AS usercode,
    c.fd_name AS xm,    k.fd_name as post,
    b.fd_shr AS shr,     CONVERT(nvarchar(100), CAST(b.fd_shmobile AS decimal(38,0))) AS mobile,     b.fd_xxshdz AS shdz, f.fd_name AS zqmc, 
    d .fd_name AS bm, b.fd_rq AS rq,     a.fd_ypmc as wlbh,a.fd_ypmc_text AS wlmc,
    a.fd_cklx as cklx, a.fd_dj AS dj,     a.fd_sqsl AS sl
FROM
         ekp_tgj_lrxcpsq_mx a
    join ekp_tgj_lrxcpsq b on a.fd_parent_id = b.fd_id  
    join sys_org_element c on b.fd_tjr = c.fd_id  
    left join sys_org_element d on b.fd_tjrbm = d .fd_id
    join km_review_main e on e.fd_id = b.fd_id 
    left join sys_org_element f on d .fd_parentid = f.fd_id
    join km_review_main h on b.fd_id = h.fd_id
    left join sys_org_person i on b.fd_tjr = i.fd_id
    left join sys_org_post_person j on i.fd_id = j.fd_personid
    left join sys_org_element k on j.fd_postid = k.fd_id
WHERE
 a.fd_sqsl IS NOT NULL 
  and  e.doc_status IN ('20','30','31') 
    AND a.fd_ypmc_text IS NOT NULL 
    AND b.fd_rq > '202302'