【in - exists-join(distinct)】SQL调优改写一例

相关数据已经脱敏处理

原SQL

MERGE INTO IntoTable t USING (
    SELECT
        x.PID,
        b.SEC_ID,
        b.T_SYB,
        b.E_CD,
        x.END_DATE,
        x.xh 区间级别,
        isnull(x.hj, 0) 区间人数
    FROM
        (
            SELECT
                a.PID,
                a.END_DATE,
                a.xh,
                c.hj
            FROM
                (
                    SELECT
                        PID,
                        END_DATE,
                        row_number () OVER (
                            PARTITION BY PID,
                            END_DATE
                        ORDER BY
                            END_DATE
                        ) xh
                    FROM
                        ScTable a
                    WHERE
                        EXISTS (
                            SELECT
                                *
                            FROM
                                (
                                    SELECT
                                        PID,
                                        END_DATE,
                                        COUNT(*) hj
                                    FROM
                                        ScTable
                                    GROUP BY
                                        PID,
                                        END_DATE
                                    HAVING
                                        COUNT(*) >= 5
                                ) b
                            WHERE
                                a.PID = b.PID
                            AND a.END_DATE = b.END_DATE
                        )
                    AND EXISTS (
                        SELECT
                            *
                        FROM
                            ScTable c
                        WHERE
                            UPDATE_TIME >= CONVERT (VARCHAR(20), GETDATE() - 1, 23)
                        AND R_TYPE = '5'
                        AND a.PID = c.PID
                        AND a.END_DATE = c.END_DATE
                    )
                    AND a.DR = '1'
                ) a
            LEFT JOIN (
                SELECT
                    PID,
                    END_DATE,
                    jb,
                    COUNT(*) hj
                FROM
                    (
                        SELECT
                            PID,
                            END_DATE,
                            mm,
                            CASE
                        WHEN mm >= 0
                        AND mm <= 200000 THEN
                            5
                        WHEN mm > 200000
                        AND mm <= 500000 THEN
                            4
                        WHEN mm > 500000
                        AND mm <= 1000000 THEN
                            3
                        WHEN mm > 1000000
                        AND mm <= 2000000 THEN
                            2
                        WHEN mm > 2000000 THEN
                            1
                        END jb
                        FROM
                            (
                                SELECT
                                    PID,
                                    END_DATE,
                                    CASE
                                WHEN A_REWARD IS NULL THEN
                                    0