按月左关联缺失的每天
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