oracle复杂sql，截取某个字段并统计次数

隐患上报

WITH D AS (
    SELECT
        T .*, SUBSTR (T .fullcode, 0, 6) suborgcode
    FROM
        sc_ot_potential_risk T
    WHERE
        (
            T .ISDELETE IS NULL
            OR T .ISDELETE = '0'
        )
    AND T .REPORT != '4'
    AND T .CREATETIME >= '2019-01-01'
    AND T .CREATETIME <= '2019-01-29'
) SELECT
    o.fullname,
    o.fullcode,
    NVL (appreport1.num, '0') report_num,
    NVL (appreport2.num, '0') app_report_num
FROM
    sc_org_ims o
LEFT JOIN (
    SELECT
        COUNT (1) num,
        D .suborgcode
    FROM
        D
    GROUP BY
        D .suborgcode
) appreport1 ON o.fullcode = appreport1.suborgcode
LEFT JOIN (
    SELECT
        COUNT (1) num,
        D .suborgcode
    FROM
        D
    WHERE
        D .sfrom = '2'
    GROUP BY
        D .suborgcode
) appreport2 ON o.fullcode = appreport2.suborgcode
WHERE
    o.parentcode = '0'
AND o.fullcode != '320000'
ORDER BY
    o.fullcode

查询结果

image.png

隐患上报城市下的二级目录

WITH D AS (
    SELECT
        T .*, T.fullcode AS suborgcode
    FROM
        sc_ot_potential_risk T
    WHERE
        (
            T .ISDELETE IS NULL
            OR T .ISDELETE = '0'
        )
    AND T .REPORT != '4'
    AND T .CREATETIME >= '2019-01-01'
    AND T .CREATETIME <= '2019-01-29'
) SELECT
    o.fullname,
    o.fullcode,
    NVL (appreport1.num, '0') report_num,
    NVL (appreport2.num, '0') app_report_num
FROM
    sc_org_ims o
LEFT JOIN (
    SELECT
        COUNT (1) num,
        D .suborgcode
    FROM
        D
    GROUP BY
        D .suborgcode
) appreport1 ON o.fullcode = appreport1.suborgcode
LEFT JOIN (
    SELECT
        COUNT (1) num,
        D .suborgcode
    FROM
        D
    WHERE
        D .sfrom = '2'
    GROUP BY
        D .suborgcode
) appreport2 ON o.fullcode = appreport2.suborgcode
WHERE
    o.parentcode != '0'
AND o.FULLCODE != '320000'
AND o.FULLCODE LIKE '%320100%'
ORDER BY
    o.fullcode

查询结果

image.png

SC_ORG_IMS表

SC_ORG_IMS表.png

SC_OT_POTENTIAL_RISK表

SC_OT_POTENTIAL_RISK表.png