查询当前时间往前任意月份,年份的数据,并按月份分组,即使没有数据,也将月份显示出来
年份则将“interval 1 month”改为“interval 1 year”
select t.time_date ctime,name from
(SELECT DATE_FORMAT(date_sub(curdate(),interval 6 month),'%Y-%m') as time_date
union all
SELECT DATE_FORMAT(date_sub(curdate(),interval 5 month),'%Y-%m') as time_date
union all
SELECT DATE_FORMAT(date_sub(curdate(),interval 4 month),'%Y-%m') as time_date
union all
SELECT DATE_FORMAT(date_sub(curdate(),interval 3 month),'%Y-%m') as time_date
union all
SELECT DATE_FORMAT(date_sub(curdate(),interval 2 month),'%Y-%m') as time_date
union all
SELECT DATE_FORMAT(date_sub(curdate(),interval 1 month),'%Y-%m') as time_date) t LEFT JOIN
(select count(name) name,pubtime ctime from user where DATE_FORMAT(pubtime,'%Y-%m') > DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 7 month),'%Y-%m') group by ctime) u on t.time_date = u.ctime