直接上代码,很简单,自己悟:
<!--新客短信促活统计列表-->
<select id="findSmsMarketingTotalListByQuery" resultType="com.rand.safc.common.model.promotion.entity.SmsMarketingTotalListVO"
parameterType="com.rand.safc.common.model.promotion.entity.SmsMarketingQuery">
select
rsm.id,rsm.batch_no batchNo,rsm.theme,rsm.client_total clientTotal,rsm.indate,
date_format(rsm.create_time,'%Y-%m-%d %H:%i') createTime,
COUNT(rsmo.status in(1,2,null)) as purchaseCount,COUNT(rsmo.status=2 or null) as loanCount,
convert(COUNT(rsmo.status=2 or null)/rsm.client_total,decimal(15,4))*100 as loanRate
from r_sms_marketing rsm
left join r_sms_marketing_order rsmo on rsmo.sms_marketing_id = rsm.id
<where> rsm.status =1
<if test="batchNo != null and batchNo != ''">
and batch_no = #{batchNo}
</if>
<if test="theme != null and theme != ''">
and theme like concat('%',#{theme},'%')
</if>
<if test="beginTime != null and endTime != null and beginTime != '' and endTime != ''">
and date_format(create_time,'%Y-%m-%d') between date_format(#{beginTime},'%Y-%m-%d') and date_format(#{endTime},'%Y-%m-%d')
</if>
</where>
GROUP BY rsm.id
</select>