【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)