Mybatis中的动态SQL标签之include

喻米粒0622

于 2025-03-25 08:00:00 发布

阅读量165

点赞数 2

文章标签： mybatis sql 算法

本文链接：https://blog.youkuaiyun.com/m0_60594323/article/details/146452922

版权

<include> include可以把大量的重复代码整理起来,当使用的时候直接include即可,减少重复代码的编写。这里是将太长的语句拆分出来，本质是一样的

  <!-- 查询促销活动列表 -->
    <sql id="selectPromotionFields">
        SELECT id,
               channel,
               1 AS promotionType,
               NAME,
               promotion_code AS promotionSn,
               knuma AS promotionKnuma,
               start_time,
               end_time,
               STATUS,
               create_by,
               create_time,
               update_time
        FROM eb_promotion
        WHERE audit_status = 1 AND datastatus = 1
    </sql>

 <!-- 查询福袋活动列表 -->
    <sql id="selectBlessBagPromotionFields">
        SELECT bagPromotion.id,
               1202                     AS channel,
               2                        AS promotionType,
               bagPromotion.NAME,
               bagPromotion.promotion_sn AS promotionSn,
               bagPromotion.promotion_knuma AS promotionKnuma ,
               min(bagBatch.start_time) AS start_time,
               max(bagBatch.end_time)   AS end_time,
               bagPromotion.STATUS,
               bagPromotion.create_by,
               bagPromotion.create_time,
               bagPromotion.update_time
         FROM eb_blessing_bag_promotion bagPromotion
              LEFT JOIN eb_blessing_bag_batch bagBatch ON bagBatch.blessing_bag_id =             
              bagPromotion.id
        WHERE bagPromotion.audit_status = 1 AND bagPromotion.datastatus = 1
        GROUP BY bagPromotion.id
    </sql>

下面将上述两个整合

<select id="queryPromotionAndBlessBagPromotionList"
	resultType="com.yyx.common.response.promotion.PromotionResponse">
    SELECT * FROM (
    <include refid="selectPromotionFields"/>
    UNION ALL
    <include refid="selectBlessBagPromotionFields"/>
    ) prom
    <where>
	    <if test="channel != null and channel != ''">
		    AND prom.channel = #{channel}
	    </if>
	    <if test="name != null and name != ''">
		    AND prom.name LIKE CONCAT('%', #{name} '%')
	    </if>
	    <if test="promotionSn != null and promotionSn != ''">
		    AND prom.promotionSn LIKE CONCAT('%', #{promotionSn} '%')
	    </if>
    	<if test="startTime!=null and startTime!='' and endTime != null and endTime != ''">
		    AND GREATEST(prom.start_time, #{startTime}) &lt;= LEAST(prom.end_time, #    
           {endTime})
	    </if>
    </where>
    ORDER BY prom.create_time DESC
</select>