mybatis 常用查询语句

<select id="getKzOrderList" resultMap="kzOrderDTO" parameterClass="java.util.Map">
SELECT
ko.id,
ko.order_code,
ko.sum_amout,
ko.funds_amout,
ko.product_amout,
ko.funds_pay,
ko.online_pay,
ko.order_pay,
ko.shipping,
ko.buyer_aid,
ko.buyer_bid,
ko.buyer_uid,
ko.buyer_mid,
ko.buyer_name,
ko.buyer_compName,
ko.buyer_tel,
ko.buyer_phone,
ko.buyer_email,
ko.buyer_address,
ko.buyer_postcode,
ko.seller_aid,
ko.seller_bid,
ko.seller_uid,
ko.seller_name,
ko.seller_compName,
ko.post_ip,
ko.post_time,
ko.order_time,
ko.ostock_status,
ko.ostock_status_title,
ko.deliver_status,
ko.deliver_status_status,
ko.pay_status,
ko.pay_status_title,
ko.order_status,
ko.order_status_title,
ko.order_source,
ko.order_type,
ko.buyer_remarks,
ko.seller_remarks,
ko.is_partial_payment,
ko.is_have_invoice,
ko.IS_DEL,
ko.CREATE_BY,
ko.CREATE_TIME,
ko.UPDATE_BY,
ko.UPDATE_TIME,
ko.BID,
ko.IS_DISCUSS,
ko.PAY_WAY,
ko.PARAM1,
ko.PARAM2,
ko.PARAM3,
ko.PARAM4,
ko.PARAM5,
ko.DISPATCH_TIME,
ko.DISCOUNT_VALUE,
ko.GIFT_PRODUCT_NUM,
ko.IS_PROMOTION
FROM
kz_order ko
LEFT JOIN (SELECT *, MAX(p.id) FROM kz_order_proceeds p GROUP BY p.order_id) kop ON ko.order_code = kop.order_code
WHERE 1=1
<isNotEmpty prepend="and" property="id">
ko.id=#id#
</isNotEmpty>
<isNotEmpty prepend="and" property="orderCode">
ko.order_code like '%$orderCode$%'
</isNotEmpty>
<isNotEmpty prepend="and" property="_quickSearch">
(ko.buyer_name like '%$_quickSearch$%' or ko.order_code like '%$_quickSearch$%')
</isNotEmpty>
<isNotEmpty prepend="and" property="tids">
ko.order_code IN ($tids$)
</isNotEmpty>
<isNotEmpty prepend="and" property="sumAmout">
ko.sum_amout=#sumAmout#
</isNotEmpty>
<isNotEmpty prepend="and" property="sumAmoutBegin">
ko.sum_amout<![CDATA[ >= ]]>#sumAmoutBegin#
</isNotEmpty>
<isNotEmpty prepend="and" property="sumAmoutEnd">
ko.sum_amout<![CDATA[ <= ]]>#sumAmoutEnd#
</isNotEmpty>
<isNotEmpty prepend="and" property="fundsAmout">
ko.funds_amout=#fundsAmout#
</isNotEmpty>
<isNotEmpty prepend="and" property="productAmout">
ko.product_amout=#productAmout#
</isNotEmpty>
<isNotEmpty prepend="and" property="fundsPay">
ko.funds_pay=#fundsPay#
</isNotEmpty>
<isNotEmpty prepend="and" property="onlinePay">
ko.online_pay=#onlinePay#
</isNotEmpty>
<isNotEmpty prepend="and" property="orderPay">
ko.order_pay=#orderPay#
</isNotEmpty>
<isNotEmpty prepend="and" property="shipping">
ko.shipping=#shipping#
</isNotEmpty>
<isNotEmpty prepend="and" property="buyerAid">
ko.buyer_aid=#buyerAid#
</isNotEmpty>
<isNotEmpty prepend="and" property="buyerBid">
ko.buyer_bid=#buyerBid#
</isNotEmpty>

<isNotEmpty prepend="and" property="bidList">
ko.buyer_bid IN
<iterate property="bidList" conjunction="," open="(" close=")">
#bidList[]#
</iterate>
</isNotEmpty>

<isNotEmpty prepend="and" property="buyerUid">
ko.buyer_uid=#buyerUid#
</isNotEmpty>
<isNotEmpty prepend="and" property="buyerMid">
ko.buyer_mid=#buyerMid#
</isNotEmpty>
<isNull property="buyerMid">
<isNotEmpty property="buyerMids">  类似SQL 的 in（）
<![CDATA[ and ko.buyer_mid in ]]>
<iterate property="buyerMids" conjunction="," open="(" close=")">
#buyerMids[]#
</iterate>
</isNotEmpty>
</isNull>
<isNotEmpty prepend="and" property="buyerName">
ko.buyer_name=#buyerName#
</isNotEmpty>
<isNotEmpty prepend="and" property="buyerCompname">
ko.buyer_compName like '%$buyerCompname$%'   模糊查询
</isNotEmpty>
<isNotEmpty prepend="and" property="buyerTel">
ko.buyer_tel=#buyerTel#
</isNotEmpty>
<isNotEmpty prepend="and" property="buyerPhone">
ko.buyer_phone=#buyerPhone#
</isNotEmpty>
<isNotEmpty prepend="and" property="buyerEmail">
ko.buyer_email=#buyerEmail#
</isNotEmpty>
<isNotEmpty prepend="and" property="buyerAddress">
ko.buyer_address=#buyerAddress#
</isNotEmpty>
<isNotEmpty prepend="and" property="buyerPostcode">
ko.buyer_postcode=#buyerPostcode#
</isNotEmpty>
<isNotEmpty prepend="and" property="sellerAid">
ko.seller_aid=#sellerAid#
</isNotEmpty>
<isNotEmpty prepend="and" property="sellerBid">
ko.seller_bid=#sellerBid#
</isNotEmpty>
<isNotEmpty prepend="and" property="sellerUid">
ko.seller_uid=#sellerUid#
</isNotEmpty>
<isNotEmpty prepend="and" property="sellerName">
ko.seller_name=#sellerName#
</isNotEmpty>
<isNotEmpty prepend="and" property="sellerCompname">
ko.seller_compName=#sellerCompname#
</isNotEmpty>
<isNotEmpty prepend="and" property="postIp">
ko.post_ip=#postIp#
</isNotEmpty>
<isNotEmpty prepend="and" property="postTime">
ko.post_time=#postTime#
</isNotEmpty>

<isNotEmpty prepend="and" property="invalidationTime">
<![CDATA[ ko.post_time <= #invalidationTime# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="beginTime">
ko.order_time <![CDATA[ >= ]]>
#beginTime#
</isNotEmpty>
<isNotEmpty prepend="and" property="endTime">
ko.order_time <![CDATA[ <= ]]>
#endTime#
</isNotEmpty>

<isNotEmpty prepend="and" property="orderTime">
ko.order_time=#orderTime#
</isNotEmpty>
<isNotEmpty prepend="and" property="ostockStatus">
ko.ostock_status=#ostockStatus#
</isNotEmpty>
<isNull property="ostockStatus">
<isNotEmpty property="ostockStatusIds">
<![CDATA[ and ko.ostock_status in ]]>
<iterate property="ostockStatusIds" conjunction="," open="(" close=")">
#ostockStatusIds[]#
</iterate>
</isNotEmpty>
</isNull>
<isNotEmpty prepend="and" property="ostockStatusTitle">
ko.ostock_status_title=#ostockStatusTitle#
</isNotEmpty>
<isNotEmpty prepend="and" property="deliverStatus">
ko.deliver_status=#deliverStatus#
</isNotEmpty>
<isNotEmpty prepend="and" property="deliverStatusStatus">
ko.deliver_status_status=#deliverStatusStatus#
</isNotEmpty>
<isNotEmpty prepend="and" property="payStatus">
<isEqual property="payStatus" compareValue="0">
ko.pay_status=0 and (kop.is_del=1 or kop.is_audit is null)
</isEqual>
<isEqual property="payStatus" compareValue="1">
ko.pay_status=#payStatus#
</isEqual>
<isEqual property="payStatus" compareValue="2">
ko.pay_status=0 and kop.is_audit=0 and kop.is_del=0
</isEqual>
</isNotEmpty>
<isNotEmpty prepend="and" property="payStatusTitle">
ko.pay_status_title=#payStatusTitle#
</isNotEmpty>
<isNotEmpty prepend="and" property="orderStatus">
ko.order_status=#orderStatus#
</isNotEmpty>
<isNotEmpty prepend="and" property="filterIsAudit">
(kop.is_audit = 1 or kop.is_audit is null)
</isNotEmpty>
<isNotEmpty prepend="and" property="orderStatus4App">
<!-- ko.order_status <![CDATA[ < 6 ]]> -->
ko.pay_status = 0 and ko.order_status != 9
</isNotEmpty>
<isNotEmpty prepend="and" property="orderStatus4AppIsManager">
<!-- ( ko.order_status <![CDATA[ < 2 ]]> OR ko.order_status = '7' ) -->
(ko.order_status <![CDATA[ < 2 ]]>)
</isNotEmpty>
<isNull property="orderStatus">
<isNotEmpty property="orderStatuses">
<![CDATA[ and order_status in ]]>
<iterate property="orderStatuses" conjunction="," open="(" close=")">
#orderStatuses[]#
</iterate>
</isNotEmpty>
</isNull>
<isNotEmpty prepend="and" property="orderStatusTitle">
ko.order_status_title=#orderStatusTitle#
</isNotEmpty>
<isNotEmpty prepend="and" property="orderSource">
ko.order_source=#orderSource#
</isNotEmpty>
<isNotEmpty prepend="and" property="orderType">
ko.order_type=#orderType#
</isNotEmpty>
<isNotEmpty prepend="and" property="buyerRemarks">
ko.buyer_remarks=#buyerRemarks#
</isNotEmpty>
<isNotEmpty prepend="and" property="sellerRemarks">
ko.seller_remarks=#sellerRemarks#
</isNotEmpty>
<isNotEmpty prepend="and" property="isPartialPayment">
ko.is_partial_payment=#isPartialPayment#
</isNotEmpty>
<isNotEmpty prepend="and" property="isHaveInvoice">
ko.is_have_invoice=#isHaveInvoice#
</isNotEmpty>
<isNotEmpty prepend="and" property="isDel">
ko.IS_DEL=#isDel#
</isNotEmpty>
<isNotEmpty prepend="and" property="createBy">
ko.CREATE_BY=#createBy#
</isNotEmpty>
<isNotEmpty prepend="and" property="createTime">
ko.CREATE_TIME=#createTime#
</isNotEmpty>
<isNotEmpty prepend="and" property="updateBy">
ko.UPDATE_BY=#updateBy#
</isNotEmpty>
<isNotEmpty prepend="and" property="updateTime">
ko.UPDATE_TIME=#updateTime#
</isNotEmpty>
<isNotEmpty prepend="and" property="bid">
ko.BID=#bid#
</isNotEmpty>
<isNotEmpty prepend="and" property="isDiscuss">
ko.IS_DISCUSS=#isDiscuss#
</isNotEmpty>
<isNotEmpty prepend="and" property="payWay">
ko.PAY_WAY=#payWay#
</isNotEmpty>
<isNotEmpty prepend="and" property="param1">
ko.PARAM1=#param1#
</isNotEmpty>
<isNotEmpty prepend="and" property="param2">
ko.PARAM2=#param2#
</isNotEmpty>
<isNotEmpty prepend="and" property="param3">
ko.PARAM3=#param3#
</isNotEmpty>
<isNotEmpty prepend="and" property="param4">
ko.PARAM4=#param4#
</isNotEmpty>
<isNotEmpty prepend="and" property="param5">
ko.PARAM5=#param5#
</isNotEmpty>
<isNotEmpty prepend="and" property="orderStatusList">
ko.order_status IN ($orderStatusList$)
</isNotEmpty>
<isNotEmpty prepend="and" property="dispatchTime">
ko.DISPATCH_TIME=#dispatchTime#
</isNotEmpty>
<isNotEmpty prepend="and" property="noCancel">
ko.order_status <![CDATA[ <> ]]>
-1
</isNotEmpty>

<isNull property="id">
<isNotEmpty property="oids">
<![CDATA[ and ko.id in ]]>
<iterate property="oids" conjunction="," open="(" close=")">
#oids[]#
</iterate>
</isNotEmpty>
</isNull>
<isNotEmpty prepend="and" property="buyerBids">
ko.buyer_bid IN ($buyerBids$)
</isNotEmpty>

<isNotEmpty property="groupBuyerBid">
group by buyer_bid
</isNotEmpty>

<isNull property="orderBy">
order by id desc
</isNull>

<isNotEmpty prepend=" " property="orderBy">
<isEqual property="orderBy" compareValue="0">
order by id desc
</isEqual>
<isEqual property="orderBy" compareValue="1">  类似只有orderby = 1 时动态拼接下面的语句。
order by post_time desc
</isEqual>
<isEqual property="orderBy" compareValue="2">
order by post_time asc
</isEqual>
<isEqual property="orderBy" compareValue="3">
order by sum_amout desc
</isEqual>
<isEqual property="orderBy" compareValue="4">
order by sum_amout asc
</isEqual>
</isNotEmpty>

<isNotEmpty property="pageNo">
<isNotEmpty property="pageSize">
LIMIT #pageNo#,#pageSize#
</isNotEmpty>
</isNotEmpty>
</select>