mysql关于根据日期统计月份整合数据的相关语句

表结构

select

      DATE_FORMAT(ur.create_time, '%Y-%m') AS month,
      count(case redpacket_type when 1 then 1 else null end  ) as '推荐人数',
sum(case redpacket_type when 1 then ur.price else 0 end  ) as '推荐红包金额',
      count(case redpacket_type when 2 then 2 else null end  ) as '幸福之家人数',
sum(case redpacket_type when 2 then ur.price else 0 end  ) as '幸福之家金额'
    from u_redpacket_detail ur
    where ur.deleted = 0

    group by month

个人理解：Count中case redpacket_type when 1 为筛选条件，然后统计为1时的人数，同理sum也是这么理解

效果图