【mybatis 如何写union和union查询】

select d.* from (
select a.CheckType,b.UserName,a.CheckNumber, a.PayName ,a.PayBank,a.PayBankNumber,a.PayMoney, a.CheckTime,a.CheckState ,a.PayFee
from deal_check a , accounts_users b
where a.userid=b.userid and 1=1
and DATE_FORMAT(a.CheckTime,'%Y-%m-%d') >= '2017-04-01'
and DATE_FORMAT(a.CheckTime,'%Y-%m-%d') <= '2017-04-10'
and CheckState=3
order by a.CheckTime desc limit 0,10 ) d
union
select e.* from (
select a.CheckType,b.UserName,a.CheckNumber, a.PayName ,a.PayBank,a.PayBankNumber,a.PayMoney, a.CheckTime,a.CheckState ,a.PayFee
from deal_check a , agent b
where a.userid=b.userid and 1=1
and DATE_FORMAT(a.CheckTime,'%Y-%m-%d') >= '2017-04-01'
and DATE_FORMAT(a.CheckTime,'%Y-%m-%d') <= '2017-04-10'
and CheckState=3
order by a.CheckTime desc limit 0,10
) e

=======================================================================

 

<select id="selectDealshow" resultMap="BaseResultMap">
select c.* from (
select a.CheckType,b.UserName,a.CheckNumber, a.PayName
,a.PayBank,a.PayBankNumber,a.PayMoney,
a.CheckTime,a.CheckState
,a.PayFee from deal_check a , accounts_users b where a.userid=b.userid
and 1=1
<if test="userName !=null and userName !=''">
and userName=#{userName}
</if>
<if test="start !=null and start !=''">
and DATE_FORMAT(a.CheckTime,'%Y-%m-%d')<![CDATA[>=]]>#{start}
</if>
<if test="end !=null and end !=''">
and DATE_FORMAT(a.CheckTime,'%Y-%m-%d')<![CDATA[<=]]>#{end}
</if>
<if test="checkType !=null and checkType !=0">
and CheckType=#{checkType}
</if>
<if test="CheckState !=null and CheckState !=0">
and CheckState=#{CheckState}
</if>
<if test="CheckState ==null or CheckState ==0">
and CheckState in (-1,1,2,3)
</if>
<if test="payName !=null and payName !=''">
and a.PayName=#{payName}
</if>
<if test="payBankNumber !=null and payBankNumber !=''">
and PayBankNumber=#{payBankNumber}
</if>
<if test="orderNumber !=null and orderNumber !=''">
and CheckNumber=#{orderNumber}
</if>
order by a.CheckTime desc limit ${target},${pageSize}
) c
union
select d.* from (
select a.CheckType,b.UserName,a.CheckNumber, a.PayName
,a.PayBank,a.PayBankNumber,a.PayMoney,
a.CheckTime,a.CheckState
,a.PayFee from deal_check a , agent b where a.userid=b.userid
and 1=1
<if test="userName !=null and userName !=''">
and userName=#{userName}
</if>
<if test="start !=null and start !=''">
and DATE_FORMAT(a.CheckTime,'%Y-%m-%d')<![CDATA[>=]]>#{start}
</if>
<if test="end !=null and end !=''">
and DATE_FORMAT(a.CheckTime,'%Y-%m-%d')<![CDATA[<=]]>#{end}
</if>
<if test="checkType !=null and checkType !=0">
and CheckType=#{checkType}
</if>
<if test="CheckState !=null and CheckState !=0">
and CheckState=#{CheckState}
</if>
<if test="CheckState ==null or CheckState ==0">
and CheckState in (-1,1,2,3)
</if>
<if test="payName !=null and payName !=''">
and a.PayName=#{payName}
</if>
<if test="payBankNumber !=null and payBankNumber !=''">
and PayBankNumber=#{payBankNumber}
</if>
<if test="orderNumber !=null and orderNumber !=''">
and CheckNumber=#{orderNumber}
</if>
order by a.CheckTime desc limit ${target},${pageSize}
) d
</select>