在Oracle中将设定条件的in语句改写成exists语句

最新推荐文章于 2024-06-26 09:26:22 发布

ybcljay

最新推荐文章于 2024-06-26 09:26:22 发布

阅读量2.8k

点赞数 1

CC 4.0 BY-SA版权

本文链接：https://blog.youkuaiyun.com/ybcljay/article/details/79579458

本文介绍了一个具体的SQL查询优化案例，通过将IN子句替换为EXISTS结合子查询的方式，提高了查询效率。原查询使用IN子句匹配多个产品ID，优化后的查询则采用EXISTS条件进行替代，以减少数据处理量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

改写前：

SELECT g.USER_CARDNO userCardno, p.PREMIUM
            FROM POLICIES p
            INNER JOIN gsuser g ON p.user_id = g.user_id
            INNER JOIN gsuser gs ON gs.user_account = g.user_cardno
            WHERE p.create_time >= to_date(#{treasure.startDate}, 'yyyy-mm-dd')
            AND p.create_time <= sysdate
            AND p.product_id IN (9,13,14,186,187,188,190,202,203)
            AND p.policyflag = '4'
            ORDER BY g.user_cardno,p.create_time ASC;

改写后：

SELECT g.USER_CARDNO userCardno, p.PREMIUM

            FROM POLICIES p
            INNER JOIN gsuser g ON p.user_id = g.user_id
            INNER JOIN gsuser gs ON gs.user_account = g.user_cardno
            WHERE p.create_time >= to_date('2018-01-01', 'yyyy-mm-dd')
            AND p.create_time <= sysdate
            AND EXISTS(
                select 1 from
                (select 9 a from dual UNION all select 13 a from dual UNION all select 14 a from dual UNION all
                select 186 a from dual UNION all select 187 a from dual UNION all select 188 a from dual UNION all
                select 190 a from dual UNION all select 202 a from dual UNION all select 203 a from dual
                )where p.product_id = a
            )
            AND p.policyflag = '4'

ORDER BY g.user_cardno,p.create_time ASC;