sql优化

xieb

2024-01-29 bf46df90e604f2bf114527f1ed6bca89c231926b

sql优化

 skjcmanager/skjcmanager-service/skjcmanager-sm/src/main/java/cn/gistack/sm/damSecurity/mapper/DamSecurityMapper.xml

@@ -316,114 +316,153 @@

    </select>
    <select id="listRegionCountOptimize" resultType="cn.gistack.sm.damSecurity.vo.DamSecurityStatistics">

        SELECT allNum,errorNum, allNum-errorNum normalNum,adName,adCode FROM
        (

        SELECT
        (
        SELECT COUNT(*) FROM
        (
        SELECT
        a."guid",
        a."name",
        a."eng_scal",
        case when b."ad_grad" = 4 THEN b."ad_code" ELSE NULL END AS "town_ad_code",
        case when b."ad_grad" = 4 THEN b."ad_name" ELSE NULL END AS "town_ad_name",
        case when b."ad_grad" = 4 THEN b."order_id" ELSE NULL END AS "town_order_id",
        case when b."ad_grad" = 3 THEN b."ad_code"
        when c."ad_grad" = 3 THEN c."ad_code" END AS "county_ad_code",
        case when b."ad_grad" = 3 THEN b."ad_name"
        when c."ad_grad" = 3 THEN C."ad_name" END AS "county_ad_name",
        case when b."ad_grad" = 3 THEN b."order_id"
        when c."ad_grad" = 3 THEN C."order_id" END AS "county_order_id",
        case when b."ad_grad" = 2 THEN b."ad_code"
        when c."ad_grad" = 2 THEN c."ad_code"
        ELSE d."ad_code" END AS "city_ad_code",
        case when b."ad_grad" = 2 THEN b."ad_name"
        when c."ad_grad" = 2 THEN c."ad_name"
        ELSE d."ad_name" END AS "city_ad_name",
        case when b."ad_grad" = 2 THEN b."order_id"
        when c."ad_grad" = 2 THEN c."order_id"
        ELSE d."order_id" END AS "city_order_id",
        case when b."ad_grad" = 1 THEN b."ad_code"
        when c."ad_grad" = 1 THEN c."ad_code"
        when d."ad_grad" = 1 THEN d."ad_code"
        ELSE e."ad_code" END AS "province_ad_code",
        case when b."ad_grad" = 1 THEN b."ad_name"
        when c."ad_grad" = 1 THEN c."ad_name"
        when d."ad_grad" = 1 THEN d."ad_name"
        ELSE e."ad_name" END AS "province_ad_name",
        case when b."ad_grad" = 1 THEN b."order_id"
        when c."ad_grad" = 1 THEN c."order_id"
        when d."ad_grad" = 1 THEN d."order_id"
        ELSE e."order_id" END AS "province_order_id"
        FROM sjzt_md."att_res_base" a
        LEFT JOIN (
        select "ad_code","ad_name",allNum,errorNum,allNum-errorNum normalNum from (
            select "ad_code","ad_name",
            (
            select count(*) from  (
            select max(NAG.dam_id) dam_id
            FROM ywxt.NKY_ALARM_GET NAG
            where DATE_FORMAT(time,'%Y-%m-%d') &gt;= #{dam.startTime} AND DATE_FORMAT(time,'%Y-%m-%d') &lt;= #{dam.endTime}
            group by NAG.dam_id
            ) NAG
            left join sjzt_md."att_res_base" res on (res."guid" = NAG.dam_id)
            left join sjzt_md."att_ad_base" ada on (ada."guid" = res."interior_ad_guid")
            left join sjzt_md."att_ad_base" adb on (adb."guid" = ada."p_ad_code")
            left join sjzt_md."att_ad_base" adc on (adc."guid" = adb."p_ad_code")
            left join sjzt_md."att_ad_base" addd on (addd."guid" = adc."p_ad_code")
            where res."interior_ad_guid" = aab."guid" or ada."guid" = aab."guid" or adb."guid" = aab."guid" or adc."guid" = aab."guid" or addd."guid" = aab."guid"


        SELECT
        distinct (dri."res_cd")
        FROM
        sjzt_dw."dim_res_info_a" dri
        LEFT JOIN sjzt_ods."dsm_spg_spprmp" dss ON dri."oth_reg_cd" = dss."rscd"
        where dss."rscd" is not null
            ) as errorNum,
            (
            SELECT COUNT(*) FROM (
            SELECT a."guid",a."name",a."eng_scal",
            CASE
            WHEN b."ad_grad" = 4 THEN
            b."ad_code"
            ELSE NULL
            END AS "town_ad_code",
            CASE
            WHEN b."ad_grad" = 4 THEN
            b."ad_name"
            ELSE NULL
            END AS "town_ad_name",
            CASE
            WHEN b."ad_grad" = 4 THEN
            b."order_id"
            ELSE NULL
            END AS "town_order_id",
            CASE
            WHEN b."ad_grad" = 3 THEN
            b."ad_code"
            WHEN c."ad_grad" = 3 THEN
            c."ad_code"
            END AS "county_ad_code",
            CASE
            WHEN b."ad_grad" = 3 THEN
            b."ad_name"
            WHEN c."ad_grad" = 3 THEN
            C."ad_name"
            END AS "county_ad_name",
            CASE
            WHEN b."ad_grad" = 3 THEN
            b."order_id"
            WHEN c."ad_grad" = 3 THEN
            C."order_id"
            END AS "county_order_id",
            CASE
            WHEN b."ad_grad" = 2 THEN
            b."ad_code"
            WHEN c."ad_grad" = 2 THEN
            c."ad_code"
            ELSE d."ad_code"
            END AS "city_ad_code",
            CASE
            WHEN b."ad_grad" = 2 THEN
            b."ad_name"
            WHEN c."ad_grad" = 2 THEN
            c."ad_name"
            ELSE d."ad_name"
            END AS "city_ad_name",
            CASE
            WHEN b."ad_grad" = 2 THEN
            b."order_id"
            WHEN c."ad_grad" = 2 THEN
            c."order_id"
            ELSE d."order_id"
            END AS "city_order_id",
            CASE
            WHEN b."ad_grad" = 1 THEN
            b."ad_code"
            WHEN c."ad_grad" = 1 THEN
            c."ad_code"
            WHEN d."ad_grad" = 1 THEN
            d."ad_code"
            ELSE e."ad_code"
            END AS "province_ad_code",
            CASE
            WHEN b."ad_grad" = 1 THEN
            b."ad_name"
            WHEN c."ad_grad" = 1 THEN
            c."ad_name"
            WHEN d."ad_grad" = 1 THEN
            d."ad_name"
            ELSE e."ad_name"
            END AS "province_ad_name",
            CASE
            WHEN b."ad_grad" = 1 THEN
            b."order_id"
            WHEN c."ad_grad" = 1 THEN
            c."order_id"
            WHEN d."ad_grad" = 1 THEN
            d."order_id"
            ELSE e."order_id"
            END AS "province_order_id"
            FROM sjzt_md."att_res_base" a
            LEFT JOIN (
            SELECT DISTINCT (dri."res_cd")
            FROM sjzt_dw."dim_res_info_a" dri
            LEFT JOIN sjzt_ods."dsm_spg_spprmp" dss
            ON dri."oth_reg_cd" = dss."rscd"
            WHERE dss."rscd" is NOT NULL
            ) f ON a."guid" = f."res_cd"
            LEFT JOIN (
            SELECT distinct(dri."res_cd")
            FROM sjzt_dw."dim_res_info_a" dri
            LEFT JOIN sjzt_ods."dsm_spg_spqnmp" dss ON trim(dss."rscd") = dri."oth_reg_cd"
            WHERE dss."rscd" is NOT NULL
            ) G ON a."guid" = g."res_cd"
            LEFT JOIN (
            SELECT distinct("res_cd")
            FROM sjzt_md."att_wy_cd_base" awcb
            WHERE awcb."cd" != '00'
            ) h ON a."guid" = h."res_cd"
            LEFT JOIN sjzt_md."att_ad_base" b ON a."interior_ad_guid" = b."guid"
            LEFT JOIN sjzt_md."att_ad_base" c ON b."p_ad_code" = c."guid"
            LEFT JOIN sjzt_md."att_ad_base" d ON c."p_ad_code" = d."guid"
            LEFT JOIN sjzt_md."att_ad_base" e ON d."p_ad_code" = e."guid"
            WHERE
            (
            f."res_cd" is NOT null
            OR g."res_cd" is NOT null
            OR h."res_cd" is NOT null
            )
            AND (d."ad_code" = aab."guid"
            OR c."ad_code" = aab."guid"
            OR b."ad_code" = aab."guid" )
            )
            ) allNum

        ) f ON a."guid" = f."res_cd"

        LEFT JOIN (

        SELECT distinct(dri."res_cd")
        from sjzt_dw."dim_res_info_a" dri
        left join sjzt_ods."dsm_spg_spqnmp" dss on trim(dss."rscd") = dri."oth_reg_cd"
        where dss."rscd" is not null

        ) G on a."guid" = g."res_cd"


        LEFT JOIN (


        SELECT distinct("res_cd") from sjzt_md."att_wy_cd_base" awcb
        where awcb."cd" != '00'


        ) h ON a."guid" = h."res_cd"
        LEFT JOIN sjzt_md."att_ad_base" b ON a."interior_ad_guid" = b."guid"
        LEFT JOIN sjzt_md."att_ad_base" c ON b."p_ad_code" = c."guid"
        LEFT JOIN sjzt_md."att_ad_base" d ON c."p_ad_code" = d."guid"
        LEFT JOIN sjzt_md."att_ad_base" e ON d."p_ad_code" = e."guid"
        where (f."res_cd" is not null OR g."res_cd" is not null OR h."res_cd" is not null) and (d."ad_code" = aab."guid"
        or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" )
        )
        ) allNum,

        (
        SELECT COUNT(*)FROM (SELECT distinct DAM_ID FROM NKY_ALARM_GET NAG
        LEFT JOIN SJZT_MD."att_res_base" arb ON arb."guid" = NAG.DAM_ID
        left join SJZT_MD."att_ad_base" b on arb."interior_ad_guid" = b."ad_code"
        left join SJZT_MD."att_ad_base" c on b."p_ad_code" = c."ad_code"
        left join SJZT_MD."att_ad_base" d on c."p_ad_code" = d."ad_code"
        where (d."ad_code" = aab."guid" or c."ad_code" = aab."guid" or b."ad_code" = aab."guid" )

        <if test="dam.startTime != null and dam.startTime != ''">
            AND DATE_FORMAT(time,'%Y-%m-%d') &gt;= #{dam.startTime}
        </if>
        <if test="dam.endTime != null and dam.endTime !='' ">
            AND DATE_FORMAT(time,'%Y-%m-%d') &lt;= #{dam.endTime}
        </if>

        )) errorNum,
        aab."ad_name" adName , aab."guid" adCode from SJZT_MD."att_ad_base" aab
        where
        <choose>
            <when test="dam.adCode != null and dam.adCode !='' ">
                "p_ad_code" = #{dam.adCode}
            </when>
            <otherwise>
                "ad_grad" = 2
            </otherwise>
        </choose>
            FROM SJZT_MD."att_ad_base" aab
            where
            <choose>
                <when test="dam.adCode != null and dam.adCode !='' ">
                    "p_ad_code" = #{dam.adCode}
                </when>
                <otherwise>
                    "ad_grad" = 2
                </otherwise>
            </choose>
        )
    </select>