MyBatis的 or 和and 问题

今天排查一个mybatis查询的问题，用的动态sql语句结果发现个问题。在mybatis中  or 的位置不同也会影响查询结果。上代码：

这是有问题的代码

    <select id="queryComissionRecord" resultType="ComissionRecordResult">
        select
        sum(t.amount) as transferAmount,
        t.agentName as agentName,
        t.tillNumber as
        tillNumber,
        t.agentRealName as realName,
        count(*) as transactionNumber,
        sum(t.comission) as
        agentComissionAmount,
        sum(t.parentComission) as
        superAgentComissionAmount

        from comission_record t

        where
        1=1
        <if test="name != null and name != ''">
            and t.agentName=#{name} or t.agentRealName=#{name}
        </if>
        <if test="tillNumber != null and tillNumber != ''">
            and
            t.tillNumber=#{tillNumber}
        </if>
        and t.parentTillNumber=#{supAgentTillNumber}
        and DATE_FORMAT(
        startTime, '%Y%m' ) = DATE_FORMAT(#{month} , '%Y%m' )
        and t.status in (0)

        group by agentName
        order by tillNumber desc

    </select>

这是没问题的代码1

    <select id="queryComissionRecord" resultType="ComissionRecordResult">
        select
        sum(t.amount) as transferAmount,
        t.agentName as agentName,
        t.tillNumber as
        tillNumber,
        t.agentRealName as realName,
        count(*) as transactionNumber,
        sum(t.comission) as
        agentComissionAmount,
        sum(t.parentComission) as
        superAgentComissionAmount

        from comission_record t

        where
        1=1
        <if test="tillNumber != null and tillNumber != ''">
            and
            t.tillNumber=#{tillNumber}
        </if>
        and t.parentTillNumber=#{supAgentTillNumber}
        and DATE_FORMAT(
        startTime, '%Y%m' ) = DATE_FORMAT(#{month} , '%Y%m' )
        and t.status in (0)
        <if test="name != null and name != ''">
            and t.agentName=#{name} or t.agentRealName=#{name}
        </if>
        group by agentName
        order by tillNumber desc

    </select>

没问题代码2

    <select id="queryComissionRecord" resultType="ComissionRecordResult">
        select
        sum(t.amount) as transferAmount,
        t.agentName as agentName,
        t.tillNumber as
        tillNumber,
        t.agentRealName as realName,
        count(*) as transactionNumber,
        sum(t.comission) as
        agentComissionAmount,
        sum(t.parentComission) as
        superAgentComissionAmount

        from comission_record t

        where
        1=1
        <if test="name != null and name != ''">
            and (t.agentName=#{name} or t.agentRealName=#{name})
        </if>
        <if test="tillNumber != null and tillNumber != ''">
            and
            t.tillNumber=#{tillNumber}
        </if>
        and t.parentTillNumber=#{supAgentTillNumber}
        and DATE_FORMAT(
        startTime, '%Y%m' ) = DATE_FORMAT(#{month} , '%Y%m' )
        and t.status in (0)
        group by agentName
        order by tillNumber desc

    </select>

 

 

两份代码的不同之处就在于，下划线部分的代码的位置不一样，如果放在前面，一旦name不为空的时候，条件开启，or 就会把后面所有的条件都当成or的一部分。结果会有很大差异。

或者也可以和第三种一样，括号优先一下。

在此小记一下，也帮助新手填坑吧。

 

转载于:https://www.cnblogs.com/Think-007/p/7566988.html