mybatis同时使用and和or

vcoy

已于 2022-09-20 17:38:26 修改

阅读量3.5k

点赞数 1

分类专栏： java mysql 文章标签： spring boot

于 2022-08-26 17:37:56 首次发布

本文链接：https://blog.csdn.net/weixin_43917045/article/details/126547782

版权

java 同时被 2 个专栏收录

6 篇文章 0 订阅

订阅专栏

mysql

5 篇文章 0 订阅

订阅专栏

1.在when或if里边and拼接（），保持与其他条件不冲突

   <select id="selectUserWashOrderList" resultType="SwOrder">
        SELECT so.*,ss.store_name
        FROM sw_order so
        LEFT JOIN sw_store ss ON so.store_id = ss.id
        where ss.deleted = 0 AND so.order_type = 0 and so.user_id = #{query.user_id}
        <choose>
            <when test="query.waitPayStatus !=null">
                and (so.status = #{query.unSettled} or so.status = #{query.waitPayStatus})
            </when>
            <when test="query.waitPayStatus == null ">
                <if test="query.status !=null">
                    and so.status = #{query.status}
                </if>
            </when>
        </choose>
        order by so.created_at desc
        limit #{pageNum},#{pageSize}
    </select>

    <select id="selectUserWashOrderListCount" resultType="Integer">
        select count(*) from (
        SELECT so.*,ss.store_name
        FROM sw_order so
        LEFT JOIN sw_store ss ON so.store_id = ss.id
        where ss.deleted = 0 AND so.order_type = 0 and so.user_id = #{query.user_id}
        <choose>
            <when test="query.waitPayStatus !=null">
                and (so.status = #{query.unSettled} or so.status = #{query.waitPayStatus})
            </when>
            <when test="query.waitPayStatus ==null ">
                <if test="query.status !=null">
                    and so.status = #{query.status}
                </if>
            </when>

        </choose>
        order by so.created_at desc
        )c
    </select>