MySQL-------exists的使用_if (merchid!=null && merchid.trim().length()!=0&&s-优快云博客

本文链接：https://blog.youkuaiyun.com/sfy_csdn/article/details/119355696

场景说明：

给我传什么就根据什么字段来查询传的字段分布在四张表上不传字段查询全部也有关于时间的区间查询

说明：如果根据merchId在tb_order_detail表中查到数据说明这条sql执行反之不执行

考虑到效率问题等问题用到exists关键字------作用判断是否查到数据查到返回true反之返回false

<select id="queryList" parameterType="com.sheca.order.orderservice.dto.req.OrderListVO"
        resultType="com.sheca.order.orderservice.dto.resp.OrderListResp">
    SELECT
    t.order_id AS "orderNo",
    t.create_time AS "createTime",
    t.order_amount AS "amount",
    t.order_status AS "orderStatus",
    t.pay_time AS "payTime",
    t.pay_status AS "payStatus",
    t.invoice_status AS "invoiceStatus",
    info.app_name as "appName"
    FROM
    tb_order t ,tb_app_info info where t.app_id=info.app_id


    <if test="orderNo != null and orderNo != ''">AND t.order_id=#{orderNo}</if>
    <if test="orderStatus != null and orderStatus != ''">AND t.order_status=#{orderStatus}</if>
    <if test="payStatus != null and payStatus != ''">AND t.pay_status=#{payStatus}</if>
    <if test="invoiceStatus != null and invoiceStatus != ''">AND t.invoice_status=#{invoiceStatus}</if>
    <if test="(maxAmount != null and maxAmount != '') and ( minAmount != null and minAmount != '')">AND t.order_amount between #{minAmount} and #{maxAmount}</if>
    <if test="appName != null and appName != ''">AND info.app_name like CONCAT('%',#{appName},'%')</if>



    <if test="beginPayTime != null">AND t.pay_time&gt;=#{beginPayTime}</if>
    <if test="endPayTime != null">AND t.pay_time&lt;#{endPayTime}</if>

    <if test="beginCreateTime != null">AND t.create_time&gt;=#{beginCreateTime}</if>
    <if test="endCreateTime != null">AND t.create_time&lt;#{endCreateTime}</if>


    <if test="(merchId != null and merchId !=  '') or (name != null and name !=  '')  or ((endMinTime != null ) or (endMaxTime != null )) or (beginMinTime != null ) or (beginMaxTime != null )">
        and exists (select * FROM tb_order_detail tod where t.order_id = tod.order_id
        <if test="merchId != null and merchId != ''">AND tod.merch_id=#{merchId}</if>
        <if test="name != null and name != ''">AND tod.name like CONCAT('%',#{name},'%')</if>

        <if test="endMinTime != null">AND tod.end_time&gt;=#{endMinTime}</if>
        <if test="endMaxTime != null">AND tod.end_time&lt;#{endMaxTime}</if>

        <if test="beginMinTime != null">AND tod.begin_time&gt;=#{beginMinTime}</if>
        <if test="beginMaxTime != null">AND tod.begin_time&lt;#{beginMaxTime}</if>

        )
    </if>
    <if test="(payWay != null and payWay != '') or (transNo != null and transNo != '') or (thirdTransNo != null and thirdTransNo != '')">
        and exists (select * FROM tb_order_pay top where t.order_id = top.order_id
        <if test="payWay != null and payWay != ''">AND top.pay_way=#{payWay}</if>
        <if test="transNo != null and transNo != ''">AND top.trans_id=#{transNo}</if>
        <if test="thirdTransNo != null and thirdTransNo != ''">AND top.third_trans_id=#{thirdTransNo}</if>

        )
    </if>

</select>

另一示例在返回值获取上又加判断也可加入排序等

<select id="selectExportOrder" parameterType="com.sheca.order.orderservice.dto.req.OrderListVO"
        resultType="com.sheca.order.orderservice.dto.resp.OrderExportResp">
    SELECT
    info.app_name as "appName",
    d.order_id AS "orderNo",
    t.create_time AS "createTime",
    t.order_amount AS "amount",
    t.order_status AS "orderStatus",
    (select p.trans_id from tb_order_pay p where t.order_id=p.order_id and p.resp_code = '0' limit 1) AS "transNo",
    (select p.pay_way from tb_order_pay p where t.order_id=p.order_id and p.resp_code = '0' limit 1) AS "payWay",
    (select p.third_trans_id from tb_order_pay p where t.order_id=p.order_id and p.resp_code = '0' limit 1) AS "thirdTransNo",
    t.pay_time AS "payTime",
    t.pay_status AS "payStatus",
    d.merch_name AS "merchName",
    d.business_type AS "businessType",
    d.price AS "price",
    d.merch_sn AS "merchSn",
    d.name AS "name",
    d.card_no AS "cardNo",
    d.begin_time AS "beginTime",
    d.end_time AS "endTime",
    t.invoice_status AS "invoiceStatus"

    FROM
    tb_order_detail d,
    tb_order t,
    tb_app_info info
    where
    t.app_id=info.app_id AND d.order_id=t.order_id
    <if test="orderNo != null and orderNo != ''">AND d.order_id=#{orderNo}</if>
    <if test="appName != null and appName != ''">AND info.app_name like concat('%',#{appName},'%')</if>
    <if test="invoiceStatus != null and invoiceStatus != ''">AND invoice_status=#{invoiceStatus}</if>

    <if test="beginPayTime != null">AND t.pay_time&gt;=#{beginPayTime}</if>
    <if test="endPayTime != null">AND t.pay_time&lt;=#{endPayTime}</if>

    <if test="beginCreateTime != null">AND t.create_time&gt;=#{beginCreateTime}</if>
    <if test="endCreateTime != null">AND t.create_time&lt;=#{endCreateTime}</if>

    <if test="minAmount != null and minAmount != ''">AND t.order_amount&gt;=#{minAmount}</if>
    <if test="maxAmount != null and maxAmount != ''">AND t.order_amount&lt;=#{maxAmount}</if>

    <if test="orderStatus != null and orderStatus != ''">AND t.order_status=#{orderStatus}</if>
    <if test="payStatus != null and payStatus != ''">AND t.pay_status=#{payStatus}</if>

    <if test="merchId != null and merchId != ''">AND d.merch_id=#{merchId}</if>
    <if test="name != null and name != ''">AND d.name like CONCAT('%',#{name},'%')</if>
    <if test="cardNo != null and cardNo != ''">AND d.card_no=#{cardNo}</if>

    <if test="beginMinTime != null">AND d.begin_time&gt;=#{beginMinTime}</if>
    <if test="beginMaxTime != null">AND d.begin_time&lt;=#{beginMaxTime}</if>

    <if test="endMinTime != null">AND d.end_time&gt;=#{endMinTime}</if>
    <if test="endMaxTime != null">AND d.end_time&lt;=#{endMaxTime}</if>

    <if test="invoiceStatus != null and invoiceStatus != ''">AND invoice_status=#{invoiceStatus}</if>

    <if test="(payWay != null and payWay != '') or (transNo != null and transNo != '') or (thirdTransNo != null and thirdTransNo != '')">
        and exists (select * FROM tb_order_pay top where t.order_id = top.order_id and top.resp_code = '0'
        <if test="payWay != null and payWay != ''">AND top.pay_way=#{payWay}</if>
        <if test="transNo != null and transNo != ''">AND top.trans_id=#{transNo}</if>
        <if test="thirdTransNo != null and thirdTransNo != ''">AND top.third_trans_id=#{thirdTransNo}</if>
        )
    </if>

</select>