报表统计，按月或者周左关联统计缺失的每天

按月左关联缺失的每天

SELECT a.date day ,IFNULL(b.count,0) from (
SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(CURRENT_DATE, INTERVAL @a DAY)) AS `date`
FROM mysql.help_topic,(SELECT @a := 0) temp
WHERE @a < DAY(LAST_DAY(CURRENT_DATE)) - DAY(CURRENT_DATE)
UNION
SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
FROM mysql.help_topic,(SELECT @s := day(CURRENT_DATE)) temp
WHERE @s > 0
ORDER BY `date` ) a LEFT JOIN 
( SELECT COUNT(1) count,DATE_FORMAT(create_time,'%Y-%m-%d') day from meta_resource_apply where DATE_FORMAT(create_time,'%Y%m')=DATE_FORMAT(CURDATE(),'%Y%m') and   data_state=1 GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d') ) b
 on a.date=b.day  ORDER BY a.date ;

获取缺失周的天数
SELECT a.date day ,IFNULL(b.count,0) from (
SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(CURRENT_DATE, INTERVAL @a DAY)) AS `date`
FROM mysql.help_topic,(SELECT @a := 0) temp
WHERE @a < 6 - WEEKDAY(CURRENT_DATE)
UNION
SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)) AS `date`
FROM mysql.help_topic,(SELECT @s := WEEKDAY(CURRENT_DATE) + 1) temp
WHERE @s > 0
ORDER BY `date` )a LEFT JOIN 
(SELECT COUNT(1) count  ,DATE_FORMAT(create_time,'%Y-%m-%d') day from meta_resource_apply where YEARWEEK(DATE_FORMAT(create_time,'%Y-%m-%d'))=YEARWEEK(NOW()) and   data_state=1 GROUP BY DATE_FORMAT(create_time,'%Y-%m-%d') ) b
 on a.date=b.day  ORDER BY a.date ;

补充缺失月

SELECT DATE_FORMAT(a.date,'%Y-%m')  day ,IFNULL(b.count,0) count from (
SELECT @a :=@a + 1 as `index`, DATE(ADDDATE(DATE_ADD(curdate(),INTERVAL -DAY(curdate())+1 DAY), INTERVAL @a MONTH)) AS `date`
FROM mysql.help_topic,(SELECT @a := 0) temp
WHERE @a < MONTH(12) - MONTH(DATE_ADD(curdate(),INTERVAL -DAY(curdate())+1 DAY))
UNION
SELECT @s :=@s - 1 as `index`, DATE(DATE_SUB(DATE_ADD(curdate(),INTERVAL -DAY(curdate())+1 DAY), INTERVAL @s MONTH)) AS `date`
FROM mysql.help_topic,(SELECT @s := MONTH(DATE_ADD(curdate(),INTERVAL -DAY(curdate())+1 DAY))) temp
WHERE @s > 0
ORDER BY `date`) a LEFT JOIN
(SELECT COUNT(1) count,DATE_FORMAT(create_time,'%Y-%m') day  from meta_resource_apply where YEAR(create_time) =YEAR(NOW())  and   data_state=1 GROUP BY DATE_FORMAT(create_time,'%Y-%m')) b
on DATE_FORMAT(a.date,'%Y-%m') =b.day  ORDER BY a.date