sql里面查询一个礼拜的数据，如果查询不到。默认填0.怎么用sql实现 查询一段时间内的按照每个小时进行统计，没有数据的补0

 SELECT date_tables.dates as name, IFNULL(temps.totalNum,0) AS totalNum
        FROM (
        SELECT DATE_FORMAT(m.date_str,'%m.%d') name, ROUND(SUM(m.mileage)/1000) AS totalNum
        FROM iot_device_miliage m
        WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= DATE(m.date_str) AND m.date_str!= DATE_FORMAT(NOW(),'%Y-%m-%d')
        GROUP BY DATE_FORMAT(m.date_str,'%m.%d')
        )
        temps
        RIGHT JOIN (
        SELECT @s :=@s + 1 AS indexs, DATE_FORMAT(DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)),'%m.%d') AS dates
        FROM mysql.help_topic,(
        SELECT @s := 0) temp
        WHERE @s < 7
        ORDER BY dates
        ) date_tables ON date_tables.dates=temps.name

实现方案是先查询进一个礼拜的数据，然后在关联起来，如果没有数据，就赋值0

 SELECT @s :=@s + 1 AS indexs, DATE_FORMAT(DATE(DATE_SUB(CURRENT_DATE, INTERVAL @s DAY)),'%m.%d') AS dates
        FROM mysql.help_topic,(
        SELECT @s := 0) temp
        WHERE @s < 7
        ORDER BY dates

这个是查询一个礼拜的数据，我们的要求是不包含当天的数据

如果需要包含当天，只要把SELECT @s :=-1) temp
        WHERE @s <6 就可以查询包含当天的一个礼拜的数据

返回的数据就是

 

 

查询一段时间内的按照每个小时进行查询，没有数据的补0

SET @beginDate=STR_TO_DATE('2020-02-27 09:47:04', '%Y-%m-%d %H:%i:%s');
SET @maxDate =STR_TO_DATE('2020-02-27 16:01:45', '%Y-%m-%d %H:%i:%s');
select '第二次推送',temps.dates '推送模板日期',ifnull(stData.count,0) '推送模板数',ifnull(activeClickTimeTable.activeClickTime,0) as '新增点击量' from (SELECT
	DATE_FORMAT(d.create_time,'%Y-%m-%d %H') as dates,
	COUNT( DISTINCT d.oid) as count 
FROM
	device_ime_push  d
	where d.create_time >=@beginDate and d.create_time <=@maxDate
GROUP BY
	DATE_FORMAT(d.create_time,'%Y-%m-%d %H')
	) as stData
	right join 
	(
SELECT DATE_FORMAT(@tempHour ,'%Y-%m-%d %H') as dates,@tempHour :=DATE_ADD(@tempHour,INTERVAL 1 HOUR)
FROM mysql.help_topic f
LEFT JOIN (SELECT @tempHour :=@beginDate) b  ON 1=1
WHERE @tempHour <=@maxDate
) temps on temps.dates=stData.dates

left join (select  DATE_FORMAT(o.create_time,'%Y-%m-%d %H') as dates,count(*) as activeClickTime from  givenins_opentimes o 
where o.url_type=2 and length(o.imei)=14
and  o.create_time >=@beginDate and o.create_time <=@maxDate
GROUP BY
	DATE_FORMAT(o.create_time,'%Y-%m-%d %H') ) activeClickTimeTable on activeClickTimeTable.dates=temps.dates

 

按小时主要是这段代码

SELECT DATE_FORMAT(@tempHour ,'%Y-%m-%d %H') as dates,@tempHour :=DATE_ADD(@tempHour,INTERVAL 1 HOUR)
FROM mysql.help_topic f
LEFT JOIN (SELECT @tempHour :=@beginDate) b  ON 1=1
WHERE @tempHour <=@maxDate