Mysql查询指定年每个月的数据统计,之前写的分组sql,如果当月无数据的话,默认不显示,距离项目需求有点不足哈...
SELECT
sum( CASE MONTH ( a.register_time ) WHEN '1' THEN 1 ELSE 0 END ) AS january,
sum( CASE MONTH ( a.register_time ) WHEN '2' THEN 1 ELSE 0 END ) AS february,
sum( CASE MONTH ( a.register_time ) WHEN '3' THEN 1 ELSE 0 END ) AS march,
sum( CASE MONTH ( a.register_time ) WHEN '4' THEN 1 ELSE 0 END ) AS april,
sum( CASE MONTH ( a.register_time ) WHEN '5' THEN 1 ELSE 0 END ) AS may,
sum( CASE MONTH ( a.register_time ) WHEN '6' THEN 1 ELSE 0 END ) AS june,
sum( CASE MONTH ( a.register_time ) WHEN '7' THEN 1 ELSE 0 END ) AS july,
sum( CASE MONTH ( a.register_time ) WHEN '8' THEN 1 ELSE 0 END ) AS august,
sum( CASE MONTH ( a.register_time ) WHEN '9' THEN 1 ELSE 0 END ) AS september,
sum( CASE MONTH ( a.register_time ) WHEN '10' THEN 1 ELSE 0 END ) AS october,
sum( CASE MONTH ( a.register_time ) WHEN '11' THEN 1 ELSE 0 END ) AS november,
sum( CASE MONTH ( a.register_time ) WHEN '12' THEN 1 ELSE 0 END ) AS december
FROM
associator a
WHERE
YEAR ( a.register_time ) = 2018;