SELECTDATE_FORMAT(create_time,'%m')AS month,COUNT(*) AS sum
FROM
GROUP BY month;
表
结果
统计十二个月每月有几条的数据
思路
第一步拿到近十二个月时间
第二步查询每月数据条数
组合查询语句 得到最终结果
sql
第一步拿到近十二个月时间
interval 1 day ,解释为将时间间隔设置为1天。
SELECT DATE_FORMAT(CURDATE(), '%Y.%m') AS month; 当前时间月
SELECT DATE_FORMAT((CURDATE() - INTERVAL 1 MONTH), '%Y.%m') AS month;上一个月时间
SELECTDATE_FORMAT(CURDATE(),'%Y.%m') AS `month1`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 1 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 2 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 3 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 4 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 5 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 6 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 7 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 8 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 9 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 10 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 11 MONTH),'%Y.%m') AS `month`
第二步查询每月数据条数
SELECTDATE_FORMAT(create_time,'%Y.%m')AS month2,COUNT(*) AS monthNum
FROM u_user u
where 1=1
GROUP BY month2
组合查询语句 得到最终结果
select a.month1 month, b.monthNum
from (SELECTDATE_FORMAT(CURDATE(),'%Y.%m') AS `month1`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 1 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 2 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 3 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 4 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 5 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 6 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 7 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 8 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 9 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 10 MONTH),'%Y.%m') AS `month`
UNION
SELECTDATE_FORMAT((CURDATE()- INTERVAL 11 MONTH),'%Y.%m') AS `month`) a
left join (SELECTDATE_FORMAT(create_time,'%Y.%m')AS month2,COUNT(*) AS monthNum
FROM u_user u
where 1=1
GROUP BY month2) b on a.month1 = b.month2
ORDER BY a.month1