NOT EXISTS 通常用于在主查询中排除某些记录，这些记录在子查询中存在

里奥酱

于 2024-10-28 14:28:49 发布

阅读量152

点赞数 2

文章标签： java 数据库服务器

本文链接：https://blog.youkuaiyun.com/weixin_45958839/article/details/143301929

版权

需求在于：当排除一堆结算单数据中，已经做过中结算的数据的时候

AND NOT EXISTS (
            SELECT *
            FROM al.common_audit ca
            INNER JOIN (
            SELECT mss.SettlementId
            FROM summary_statement mss
            INNER JOIN statement_contract mssc
            ON mss.SysNo = mssc.StatementSysNo
            WHERE
            mssc.ContractCode in <foreach collection="contractCodes" separator="," item="item" open="(" close=")">#{item}</foreach>
            ) subquery
            ON ca.BusinessId = subquery.SettlementId
            WHERE ca.CommonStatus = 1
            AND ca.ApprovalStatus = 1
            AND ca.BusinessStatus = 1
            )

用这种排除法效率高且准确