2021-04-23

记一次“exists”与“in”的使用与理解

WHERE
    ...
    AND TBL1.cus_nnd_sirk || TBL1.status IN ( 
        SELECT
            TBL1.cus_nnd_sirk || MAX(TBL1.status) 
        FROM
            BODATA.TSY120_010 TBL1 
        GROUP BY
            TBL1.cus_nnd_sirk
    ) 
WHERE
    ...
    AND TBL1.status = ( 
        SELECT
            MAX(TBL.status) AS status 
        FROM
            BODATA.TSY120_010 TBL 
        WHERE
            TBL.cus_nnd_sirk = TBL1.cus_nnd_sirk 
            AND TBL1.status = TBL.status 
        GROUP BY
            TBL.cus_nnd_sirk
    ) 
WHERE
    ...
    AND TBL1.status = ( 
        SELECT
            TBL1.status 
        FROM
            ( 
                SELECT
                    cus_nnd_sirk
                    , MAX(status) AS status 
                FROM
                    BODATA.TSY120_010 
                GROUP BY
                    cus_nnd_sirk
            ) TBL 
        WHERE
            TBL.cus_nnd_sirk = TBL1.cus_nnd_sirk 
            AND TBL1.status = TBL.status
    ) 
WHERE
    ...
    AND EXISTS ( 
        SELECT
            TBL1.status 
        FROM
            ( 
                SELECT
                    cus_nnd_sirk
                    , MAX(status) AS status 
                FROM
                    BODATA.TSY120_010 
                GROUP BY
                    cus_nnd_sirk
            ) TBL 
        WHERE
            TBL.cus_nnd_sirk = TBL1.cus_nnd_sirk 
            AND TBL1.status = TBL.status
    ) 

结论是,有明显的提升。

最后编辑于 :
©著作权归作者所有,转载或内容合作请联系作者
【社区内容提示】社区部分内容疑似由AI辅助生成,浏览时请结合常识与多方信息审慎甄别。
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

友情链接更多精彩内容