exists在sql中的妙用

最近有两处功能都用到了exists， 觉得它很好地用子查询的方式实现了对主表数据的过滤

1. 支持历史记录id查询主id

那就用子查询过滤一下，主记录对应的历史记录中要存在当前id

        <if test="!searchHistoryFlag">and cbi.is_last_submit = 1<if test="searchSubmitId != null and searchSubmitId != ''">and exists (select1frombendcompete_info twheret.vendor_id = cbi.vendor_idand t.submit_user_mobile = cbi.submit_user_mobileand t.submit_year = cbi.submit_yearand t.submit_month = cbi.submit_monthand t.submit_id = #{searchSubmitId})</if></if><if test="searchHistoryFlag"><if test="searchSubmitId != null and searchSubmitId != ''">and cbi.submit_id = #{searchSubmitId}</if></if>

2. 相同会员品牌下取最新月的数据

由于年和月是分开存储的，为了比较月份谁大谁小，那就把年乘以12跟月份相加，统一按月份进行比较
月份可以理解为12进制的数据

        select rc.* from  bendcompete_prize_receive_config rcwhere rc.prize_status = 1and not exists (select1frombendcompete_prize_receive_config rc2whererc2.member_brand_id = rc.member_brand_idand rc2.submit_year * 12 + rc2.submit_month > (rc.submit_year * 12 + rc.submit_month))