mybatis count统计加条件，除法保留两位小数，分组统计，日期格式化 between and

直接上代码，很简单，自己悟：

    <!--新客短信促活统计列表-->
    <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>