OA 统计宣传品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'