单表count 多个字段 ,按条件统计多个字段的数量
<select id="listMarketingActivities"
resultType="com.yilihuo.cloud.service.shop.manager.dto.api.storedata.MarketingActivitiesResultDto">
select
activity.member_id as memberId,
SUM(CASE WHEN (date_format(r.last_order_date,'%Y-%m-%d') BETWEEN #{paramDto.beginDate}
AND #{paramDto.endDate}) THEN 1 ELSE 0 END) AS completeCustomerCount,
SUM(CASE WHEN activity.id) as weekTotalPlays
from store_manage_activity activity
where activity.activity_type = #{activityType}
<![CDATA[
and date_sub(date_format(#{date},'%Y-%m-%d %H:%i:%s'), INTERVAL 30 DAY) <= date(activity.activity_time)
and #{date} > date(activity.activity_time)
]]>
and activity.member_id in
<foreach collection="memberIdList" item="memberId" open="(" separator="," close=")">
#{memberId}
</foreach>
group by activity.member_id
</select>
这里一定要注意sum()函数后面不要有空格,不然报一个找不到函数的错误