【LEFT JOIN 实战记录】统计查询-按主办处室区县查询纳入分析研判库

【LEFT JOIN 实战记录】统计查询-问题明细指标查询
【LEFT JOIN 实战记录】统计查询-整改事项查询（多表联合查询+多次左连接字典表）
【LEFT JOIN 实战记录】统计查询-按主办处室区县查询纳入分析研判库
【LEFT JOIN 实战记录】是否纳入市级预算项目概览界面
【LEFT JOIN 入门级实践】项目表与组织表与人员表，其中人员存在上下级关系
【LEFT JOIN 入门级实践】MYSQL 简单实现 ORACLE minus 差集函数
如何手动将ORACLE的sql建表语句转换为MYSQL的建表语句

--主办处室（区县）、事项个数、纳入分析研判库个数、占比
--利用ROWNUM，实现序列号的展示
SELECT ROWNUM AS NUM,*,
--计算百分比时，如果分母存在可以能为0的状态时，可以通过NUllIF（X,Y）函数来避免
--NULLIF（X,Y） 当X=Y时，为NULL，否则为 X
         TO_NUMBER(ANALYSIS_COUNT)/NULLIF(TO_NUMBER(ALL_PROBLEM_COUNT),0) AS ANALYSIS_RATE
 FROM (SELECT
        O.ORGNAME,
        COUNT(P.PROBLEM_UUID) AS ALL_PROBLEM_COUNT,
         COUNT(
                CASE WHEN P.ANALYSIS_TAG='1' THEN 1 ELSE NULL END
        ) AS ANALYSIS_COUNT
FROM
--此处查询分组数据，按各个表分组
     (SELECT O.ORGUUID,O.CNNAME AS ORGNAME,O.SEQUENCENO FROM SYS_ORG O JOIN SYS_ORG PO ON PO.ORGUUID = O.PARENTORGUUID
         WHERE PO.ORGCODE = '3195000003' AND (O.ORGPROP='C' OR O.ORGPROP='X' OR O.ORGPROP='H') ORDER BY O.SEQUENCENO) O
LEFT JOIN
--TODO 此处未考虑全行业项目事项
        (SELECT PROBLEM_UUID,PROBLEM_NAME, BUDGET_YEAR, DOCUMENT_TYPE,
         RESPONSIBILITY_SUBJECT, RECTIFY_STATE, OVERDUE, METHOD, UNRECTIFY_REASON_REMARK,
          UNRECTIFY_REASON, SITUATION_PLAN, REMARK, ANALYSIS_TAG, ANALYSIS_REASON, FIRST_CHECK_TAG,
          PROBLEM_NATURE, DETAILED, AMOUNT, UNIT, HANDLE_SITUATION, DEAL_MONEY, DURING_AUDIT_MONEY,
           PROMOTE_MONEY, PROMOTE_MONEY_CASH,  FINALLY_CHECK_STATE,  PROBLEM_NATURE_NAME, DETAILED_NAME,
        PRJ.PROJECT_UUID,PLAN_YEAR,PROJECT_NAME,OFFICESID,PROJECT_CLASSIFICATION FROM PROJECT PRJ
        LEFT JOIN PROBLEM ON PROBLEM.PROJECT_UUID = PRJ.PROJECT_UUID
        WHERE PRJ.IS_DEL = '0' AND PROBLEM.IS_DEL   = '0'
        ) P
ON
        P.OFFICESID =O.ORGUUID
WHERE 1=1
GROUP BY
        O.ORGUUID,
        O.ORGNAME)