按月份查询,没有数据的月份补充为0,查询结果如图:
sql:
SELECT a.month,IFNULL(b.num,0) AS num
FROM (
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH),'%Y-%m') AS month
UNION ALL
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 2 MONTH),'%Y-%m') AS month
UNION ALL
SELECT DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m') AS month
UNION ALL
SELECT DATE_FORMAT(CURDATE(),'%Y-%m') AS month
) a LEFT JOIN (
SELECT DATE_FORMAT(group_dt,'%Y-%m') AS DATETIME, count(*) AS num
FROM t1
WHERE 1=1
GROUP BY DATE_FORMAT(group_dt,'%Y-%m')
) b ON a.month = b.datetime