按周查询

SELECT a.click_week,IFNULL(b.count,0) AS count
FROM (SELECT WEEK(CURRENT_DATE,1) as click_week
		UNION ALL
		SELECT WEEK(DATE_SUB(CURRENT_DATE,INTERVAL 1 WEEK),1) as click_week
		UNION ALL
		SELECT WEEK(DATE_SUB(CURRENT_DATE,INTERVAL 2 WEEK),1) as click_week
		UNION ALL
		SELECT WEEK(DATE_SUB(CURRENT_DATE,INTERVAL 3 WEEK),1) as click_week
		UNION ALL
		SELECT WEEK(DATE_SUB(CURRENT_DATE,INTERVAL 4 WEEK),1) as click_week
		UNION ALL
		SELECT WEEK(DATE_SUB(CURRENT_DATE,INTERVAL 5 WEEK),1) as click_week
		UNION ALL
		SELECT WEEK(DATE_SUB(CURRENT_DATE,INTERVAL 6 WEEK),1) as click_week
) a left join(
		SELECT WEEK(push_date,1) as datetime,count(*) as count
		FROM `warnings`
		GROUP BY WEEK(push_date,1)
)b on a.click_week = b.datetime;

但是这种只适用于数据的日期都在同一年，如果是不同年份的9月1号，那么也会被统计，被认为属于同一周次，从而产生错误。
以下为正确写法：

SELECT a.click_week,IFNULL(b.count,0) AS count
FROM (SELECT DATE_FORMAT(CURRENT_DATE,'%Y-%U') as click_week
		UNION ALL
		SELECT DATE_FORMAT(DATE_SUB(CURRENT_DATE,INTERVAL 1 WEEK),'%Y-%U') as click_week
		UNION ALL
		SELECT DATE_FORMAT(DATE_SUB(CURRENT_DATE,INTERVAL 2 WEEK),'%Y-%U') as click_week
		UNION ALL
		SELECT DATE_FORMAT(DATE_SUB(CURRENT_DATE,INTERVAL 3 WEEK),'%Y-%U') as click_week
		UNION ALL
		SELECT DATE_FORMAT(DATE_SUB(CURRENT_DATE,INTERVAL 4 WEEK),'%Y-%U') as click_week
		UNION ALL
		SELECT DATE_FORMAT(DATE_SUB(CURRENT_DATE,INTERVAL 5 WEEK),'%Y-%U') as click_week
		UNION ALL
		SELECT DATE_FORMAT(DATE_SUB(CURRENT_DATE,INTERVAL 6 WEEK),'%Y-%U') as click_week
) a left join(
		SELECT DATE_FORMAT(push_date,'%Y-%U') as datetime,count(*) as count
		FROM `warnings`
		GROUP BY WEEK(push_date,1)
)b on a.click_week = b.datetime;

但是这样得到的’%Y-%U’中对应周数字段需要直行一次自增操作，要不然存在问题。

最新更新版本

参考链接

SELECT DATE_FORMAT(a.date,'%Y-%U') AS DATE,IFNULL(b.count,0) as count FROM
(
SELECT @cdate := DATE_ADD(@cdate,INTERVAL - 1 WEEK) `date`
FROM (SELECT @cdate :=DATE_ADD('2020-12-03', INTERVAL + 1 WEEK) FROM `warnings`) t1 
WHERE @cdate > '2020-09-01'
)a
LEFT JOIN
(
	SELECT DATE_FORMAT(w.push_date,'%Y-%U')as datetime,COUNT(*)as count FROM
	`warnings` w
	GROUP BY WEEK(w.push_date,1)
)b 
ON b.datetime= DATE_FORMAT(a.date,'%Y-%U')