Skip to main content
 首页 » 编程设计

mybatis sql参考

2022年07月18日47jyk

参考mybatis sql:

<select id="xxx" resultType="com.xxxx.xxx.vo.xx.xx" parameterType="com.xxx.xx.req.xxx">
SELECT
apply.apply_no,
apply.loan_apply_code,
cust.cust_name,
cust.id_no,
cust.mobile,
apply.loan_amount,
CONCAT(apply.init_period, CASE WHEN (apply.period_unit_code='1') then '天' WHEN (apply.period_unit_code='2') then '月' END) as init_period_str,
apply.contract_rate,
apply.apply_status,
(SELECT dict_value FROM aim_t_sys_dict_data WHERE dict_code = apply.apply_status AND dict_type = 'APPLY_STATUS' AND deleted='0' LIMIT 1) as apply_status_str,
apply.assets_code,
<!-- apply.assets_platform_code, -->
platform.platform_name,
<!-- apply.product_code, -->
product.product_name,
account.create_time,
apply.agreement_confirm_time
FROM aim_t_loan_apply apply
LEFT JOIN aim_t_cust_base cust ON apply.cust_code = cust.cust_code
LEFT JOIN aim_t_loan_assets assets ON apply.apply_no = assets.apply_no
LEFT JOIN aim_t_assets_platform platform on apply.assets_platform_code = platform.platform_code
LEFT JOIN aim_t_product product on apply.product_code = product.product_code
LEFT JOIN aim_t_cust_sub_account account on cust.id_no=account.id_no and cust.id_type=account.id_type
<where>
<if test="userPlatformCodeList != null and userPlatformCodeList.size > 0">
AND apply.assets_platform_code in
<foreach collection="userPlatformCodeList" index="userPlatformCode" item="userPlatformCode"
open="(" separator="," close=")">
#{userPlatformCode,jdbcType=VARCHAR}
</foreach>
</if>
<if test="custName != null and custName != ''">
AND cust.cust_name = #{custName,jdbcType=VARCHAR}
</if>
<if test="mobile != null and mobile != ''">
AND cust.mobile = #{mobile,jdbcType=VARCHAR}
</if>
<if test="idNo != null and idNo !=''">
AND cust.id_no = #{idNo,jdbcType=VARCHAR}
</if>
<if test="loanApplyCode != null and loanApplyCode != ''">
AND apply.loan_apply_code = #{loanApplyCode,jdbcType=VARCHAR}
</if>
<if test="assetsPlatformCode != null and assetsPlatformCode !=''">
AND apply.assets_platform_code = #{assetsPlatformCode,jdbcType=VARCHAR}
</if>
<if test="productCode != null and productCode != ''">
AND apply.product_code = #{productCode,jdbcType=VARCHAR}
</if>
<if test="initPeriod != null">
AND apply.init_period = #{initPeriod,jdbcType=INTEGER}
</if>
<if test="periodUnitCode != null and periodUnitCode != ''">
AND apply.period_unit_code = #{periodUnitCode,jdbcType=VARCHAR}
</if>
<if test="loanApplyStartTime != null and loanApplyStartTime != '' and loanApplyEndTime != null and loanApplyEndTime != ''">
AND apply.loan_apply_time BETWEEN #{loanApplyStartTime,jdbcType=VARCHAR} and #{loanApplyEndTime,jdbcType=VARCHAR}
</if>
<if test="applyStatus != null and applyStatus != ''">
AND apply.apply_status = #{applyStatus,jdbcType=VARCHAR}
</if>
<if test="approveStatus != null and approveStatus != ''">
AND apply.approve_status = #{approveStatus,jdbcType=VARCHAR}
</if>
<if test="isOpen != null and isOpen !=''">
AND apply.is_open = #{isOpen,jdbcType=VARCHAR}
</if>
<if test='agreementConfirmStatus != null and agreementConfirmStatus=="0"'>
AND (apply.agreement_confirm_time IS NULL or apply.agreement_confirm_time = '')
</if>
<if test='agreementConfirmStatus != null and agreementConfirmStatus=="1"'>
AND apply.agreement_confirm_time IS NOT NULL
</if>
<if test="loanStartTime != null and loanStartTime != ''">
AND assets.loan_time <![CDATA[>=]]> #{loanStartTime,jdbcType=VARCHAR}
</if>
<if test="loanEndTime != null and loanEndTime !=''">
AND assets.loan_time <![CDATA[<=]]> #{loanEndTime,jdbcType=VARCHAR}
</if>
AND apply.deleted = '0'


本文参考链接:https://www.cnblogs.com/maohuidong/p/10495377.html