Mybatis多表联查SQL文件

    <!--查询该部门，该门店门店有阶段性活动的数量-->
    <select id="getStageActivityCount" parameterType="com.jd.wanjia.activity.dto.StageActivityParameterDTO" resultType="int">
        SELECT
        COUNT(1)
        FROM
        activity_info i
        LEFT JOIN activity_object o ON i.activity_no = o.activity_no
        WHERE
        <!--activity_info表Type 活动类型1.开店任务2.阶段性任务3.营销任务4.图文任务5.物料任务-->
        <if test="activityInfoType!=null">
            i.type=#{activityInfoType}
        </if>
        <!--activity_info表activity_status 活动状态:0创建中,1审核中,2审核通过,3被驳回,4进行中,5已失效,6已结束-->
        <if test="activityInfoStatus!=null">
            AND i.activity_status=#{activityInfoStatus}
        </if>
        <!--对象类型1:平台商2:商家3:门店4:导购-->
        <if test="objectType!=null">
            AND i.object_type=#{objectType}
        </if>
        <!-- 查询条件里shopId对应object_id-->
        <if test="shopId!=null">
            AND (
            o.object_id =#{shopId}
            OR o.object_id is null
            )
        </if>
        <if test="departNo!=null">
            AND i.depart_no=#{departNo}
        </if>
        AND i.is_delete = 0
        AND
        (
        o.is_delete = 0
        or o.is_delete is null
        )
    </select>

    <!--查询商品是否参与正在进行中的阶段性活动的商品信息-->
<select id="queryStageActivityByParameter" parameterType="com.jd.wanjia.activity.dto.StageActivityParameterDTO" resultType="com.jd.wanjia.activity.po.ActivitySku">
    SELECT
    MAX(i.activity_no) AS activityNo,s.sku_type AS skuType,s.sku_id AS skuId, s.brand_code AS brandCode,
    s.first_cate_code AS firstCateCode, s.second_cate_code AS secondCateCode, s.third_cate_code AS thirdCateCode
    FROM
    activity_info i
    INNER JOIN activity_sku s ON i.activity_no = s.activity_no
    LEFT  JOIN activity_object o ON i.activity_no = o.activity_no
    WHERE
    <!--activity_info表Type 活动类型1.开店任务2.阶段性任务3.营销任务4.图文任务5.物料任务-->
    <if test="activityInfoType!=null">
        i.type=#{activityInfoType}
    </if>
    <!--activity_info表activity_status 活动状态:0创建中,1审核中,2审核通过,3被驳回,4进行中,5已失效,6已结束-->
    <if test="activityInfoStatus!=null">
        AND i.activity_status=#{activityInfoStatus}
    </if>
    <!--对象类型1:平台商2:商家3:门店4:导购-->
    <if test="objectType!=null">
        AND i.object_type=#{objectType}
    </if>
    <!-- 查询条件里shopId对应object_id-->
    <if test="shopId!=null">
        AND (
        o.object_id =#{shopId}
        OR o.object_id is null
        )
    </if>
    <if test="departNo!=null">
        AND
        i.depart_no=#{departNo}
    </if>
    <!--type'1.sku 2.品类'-->
    <if test="sType!=null">
        AND s.type=#{sType}
    </if>
    <!--sku_type'1.自营 2.POP',-->
    <if test="skuTypeList!=null">
        <![CDATA[ AND s.sku_type IN ]]>
        <foreach collection="skuTypeList" item="item" open="(" separator="," close=")">
            <![CDATA[#{item}]]>
        </foreach>
    </if>
    <if test="skuIdList!=null">
        <![CDATA[ AND s.sku_id IN ]]>
        <foreach collection="skuIdList" item="item" open="(" separator="," close=")">
            <![CDATA[#{item}]]>
        </foreach>
    </if>
    <if test="brandCodeList!=null">
        <![CDATA[ AND s.brand_code IN ]]>
        <foreach collection="brandCodeList" item="item" open="(" separator="," close=")">
            <![CDATA[#{item}]]>
        </foreach>
    </if>
    <if test="firstCateCodeList!=null">
        <![CDATA[ AND s.first_cate_code  IN ]]>
        <foreach collection="firstCateCodeList" item="item" open="(" separator="," close=")">
            <![CDATA[#{item}]]>
        </foreach>
    </if>
    <if test="secondCateCodeList!=null">
        <![CDATA[ AND s.second_cate_code IN ]]>
        <foreach collection="secondCateCodeList" item="item" open="(" separator="," close=")">
            <![CDATA[#{item}]]>
        </foreach>
    </if>
    <if test="thirdCateCodeList!=null">
        <![CDATA[ AND s.third_cate_code  IN ]]>
        <foreach collection="thirdCateCodeList" item="item" open="(" separator="," close=")">
            <![CDATA[#{item}]]>
        </foreach>
    </if>
    AND i.is_delete = 0
    AND s.is_delete = 0
    AND
    (
    o.is_delete = 0
    or o.is_delete is null
    )
    <if test="sType==1">
        GROUP BY s.sku_type,s.sku_id
    </if>
    <if test="sType==2">
        GROUP BY s.sku_type,s.brand_code,s.first_cate_code,s.second_cate_code,s.third_cate_code
    </if>
</select>

 

对应mapper.java