sql示例备份

 <sql id="selectBillForSellSql">
 	SELECT
		c.*
	FROM
		(
    	SELECT
    				cr.*, cd.sell_state AS rcv_state,
    				cd.fin_nm AS rcver_nm,
    				cd.seller_id AS trader_id,
    				cd.seller_nm AS trader_nm,
    				cd.sell_date AS tx_date,
    				IF(cd.pay_interest_mode='1',cd.sell_amt-cd.sell_ins,cd.sell_amt) as sell_amt,
    				cd.fixed_rate AS sell_fixed_rate,
    				cd.var_rate AS sell_var_rate,
    				cd.sell_ins,
    				cd.sell_days,
    				cd.bill_level AS LEVEL,
    				cs.puher_nm,
    				cd.remark,
    				cr1.bill_code AS root_bill_code,
    				(
    				CASE WHEN ( cd.sell_state = '3' || cd.sell_state = '2' ) AND cr.bill_state != 'S018' <!-- 融资已付款 -->
    				THEN 'S012' 
    				WHEN cd.sell_state = '4' <!-- 融资已回购 -->
    				THEN 'S068' 
    				ELSE cr.bill_state 
    			END 
    			) AS biz_bill_state,
    			cd.sell_id AS biz_id,
    			cd.pay_interest_mode,
    			cd.protocol_status,
    			cd.seller_nm as sellerNm,
    			cd.fin_nm AS finName,
    			cd.sell_date,
    			cd.financing_mode,
    			cd.fin_id AS sell_fin_id,
    			cs.puh_id,
    			cs.protocol_code as root_protocol_code,
    			cs.invoice_code as root_invoice_code,
    			cd.sell_pro_code,
    			cd.edit_time,
    			cd.sell_dj_code
    		FROM
    			bill cr
    		JOIN bill cr1 ON cr.root_id = cr1.bill_id
    		JOIN bill_publish cs ON cr.root_id = cs.bill_id
    		JOIN (SELECT * FROM bill_sell a where a.sell_id != '') cd ON cr.bill_id = cd.bill_id <!-- 查询整融和融转复核通过之后的单 -->
    		LEFT JOIN acct_tx_rec re ON cr.bill_id = re.biz_id AND re.tx_type = '5'
    		<if test="settleState !=null and &quot;1&quot;.equals(settleState.trim())">
    	   		AND (re.tx_sts != 'S' or re.tx_sts is null)
    	   	</if>
    	   	<if test="settleState !=null and &quot;2&quot;.equals(settleState.trim())">
    	   		AND c.bill_state = 'S018' AND re.tx_sts = 'S'
    	   	</if>	
    		) c 
    	<where>
    		<if test="cusNm != null and !&quot;&quot;.equals(cusNm.trim())">
    		  AND	(c.rcver_nm like #{cusNm} or c.puher_nm like #{cusNm})
    		</if>
    		<if test="sellerNm != null and !&quot;&quot;.equals(sellerNm.trim())">
    		  AND	c.sellerNm like #{sellerNm} 
    		</if>
    		<if test="rcverNm != null and !&quot;&quot;.equals(rcverNm.trim())">
    		  AND	c.rcver_nm like #{rcverNm} 
    		</if>
    		<if test="protocolCode != null and !&quot;&quot;.equals(protocolCode.trim())">
    		  AND	c.protocol_code like #{protocolCode}
    		</if>
    		<if test="finId != null and !&quot;&quot;.equals(finId.trim())">
    		  AND	c.fin_id = #{finId}
    		</if>
    		<if test="sellFinId != null and !&quot;&quot;.equals(sellFinId.trim())">
    		  AND	c.sell_fin_id = #{sellFinId}
    		</if>
    		<if test="traderId != null and !&quot;&quot;.equals(traderId.trim())">
    		  AND	c.trader_id = #{traderId}
    		</if>
    		<if test="billCode != null and !&quot;&quot;.equals(billCode.trim())">
    		  AND	c.bill_code like #{billCode}
    		</if>
    		<if test="billAmtMax != null">
    		  AND	c.bill_amt &lt;= #{billAmtMax}
    		</if>
    		<if test="billAmtMin != null">
    		  AND	c.bill_amt &gt;= #{billAmtMin}
    		</if>
    		<if test="startSndDate!= null and !&quot;&quot;.equals(startSndDate.trim())">
    		  AND	c.puh_date &gt;= #{startSndDate}
    		</if>
    		<if test="endSndDate!= null and !&quot;&quot;.equals(endSndDate.trim())">
    		  AND	c.puh_date &lt;= #{endSndDate}
    		</if>
    		
    		<if test="startExpDate != null and !&quot;&quot;.equals(startExpDate.trim())">
    		  AND	c.exp_date &gt;= #{startExpDate}
    		</if>
    		<if test="endExpDate != null and !&quot;&quot;.equals(endExpDate.trim())">
    		  AND	c.exp_date &lt;= #{endExpDate}
    		</if>
    			
    		<if test="startTxDate!= null and !&quot;&quot;.equals(startTxDate.trim())">
    		  AND	c.tx_date &gt;= #{startTxDate}
    		</if>
    		<if test="endTxDate!= null and !&quot;&quot;.equals(endTxDate.trim())">
    		  AND	c.tx_date &lt;= #{endTxDate}
    		</if>
    		<if test="bizBillState!= null and !&quot;&quot;.equals(bizBillState.trim())">
    		  AND c.biz_bill_state = #{bizBillState}
    		</if>
    		<if test="notbillStates !=null and notbillStates.length > 0">
    	  	  AND  c.bill_state not in 	<!-- 过滤掉 -->
    	  	  <foreach item="item" index="index" collection="notbillStates"
    			open="(" separator="," close=")">
    			#{item}
    		  </foreach>
    	   	</if>	
    	   		
    		<if test="billStates !=null and billStates.length > 0">
    	  	  AND   (c.bill_state in 	
    	  	  <foreach item="item" index="index" collection="billStates"
    			open="(" separator="," close=")">
    			#{item}
    		  </foreach>
    			<if test="signed">
    		  	  or c.rcv_state in ('3', '2')  <!-- 查询融资过 -->
    		   	</if>
    		   	<if test="buyFlag">
    		   		or c.rcv_state = '4'
    		   	</if>
    		  )
    	   	</if>
    	   	<if test="billStates == null or billStates.length == 0">
    	  	  <if test="signed">
    		  	  AND  c.rcv_state in ('-2','2')  <!-- 查询签收和签收过的 -->
    		  </if>
    		  <!-- <if test="!signed">
    		  	  AND  c.rcv_state not in ('0') 查询所有有效的融资记录
    		  </if> -->
    	   	</if>
    	   	AND  c.rcv_state not in ('0') <!-- 查询所有有效的融资记录-->	
    	   	
    	   	<if test="states !=null and states.length > 0">
    	  	  AND  c.bill_state in 	
    	  	  <foreach item="item" index="index" collection="states"
    			open="(" separator="," close=")">
    			#{item}
    		  </foreach>
    	   	</if>
       	</where>
       	ORDER BY c.edit_time DESC	
     </sql>