Mysql 查询当前年份每月数量总数，生成一条记录的方法

 

Mysql 进行当年12个月份总数统计，生成一条记录的方法

select
case month(bri.create_date) when '1' then COUNT(key_id) else 0 end as 1月,
case month(bri.create_date) when '2' then COUNT(key_id) else 0 end as 2月,
case month(bri.create_date) when '3' then COUNT(key_id) else 0 end as 3月,
case month(bri.create_date) when '4' then COUNT(key_id) else 0 end as 4月,
case month(bri.create_date) when '5' then COUNT(key_id) else 0 end as 5月,
case month(bri.create_date) when '6' then COUNT(key_id) else 0 end as 6月,
case month(bri.create_date) when '7' then COUNT(key_id) else 0 end as 7月,
case month(bri.create_date) when '8' then COUNT(key_id) else 0 end as 8月,
case month(bri.create_date) when '9' then COUNT(key_id) else 0 end as 9月,
case month(bri.create_date) when '10' then COUNT(key_id) else 0 end as 10月,
case month(bri.create_date) when '11' then COUNT(key_id) else 0 end as 11月,
case month(bri.create_date) when '12' then COUNT(key_id) else 0 end as 12月
from table_name bri
where date_format(bri.create_date, '%Y') = '2021'

查询结果如下：

 注： 使用时，替换table_name 和 key_id