坑：mysql 查询最近一段时间的告警sh，null用0代替

本文详细介绍了通过SQL查询实现的近一个月、近一周及近24小时的报警数据统计方法，利用日期子查询技巧，实现了对ls_alarm表中数据按日期进行汇总，以图表形式展示报警次数的变化趋势。

--最近一个月
			select  tt.date as XCLASSIFY,IFNULL(COUNT(alarm.id),0) as COUNT
			from ls_alarm as alarm
			RIGHT JOIN (
			select DATE_SUB(CURDATE(), INTERVAL aa.num DAY) as date from (
			SELECT 0 num UNION ALL
			SELECT 1 num UNION ALL
			SELECT 2 num UNION ALL
			SELECT 3 num UNION ALL
			SELECT 4 num UNION ALL
			SELECT 5 num UNION ALL
			SELECT 6 num UNION ALL
			SELECT 7 num UNION ALL
			SELECT 8 num UNION ALL
			SELECT 9 num UNION ALL
			SELECT 10 num UNION ALL
			SELECT 11 num UNION ALL
			SELECT 12 num UNION ALL
			SELECT 13 num UNION ALL
			SELECT 14 num UNION ALL
			SELECT 15 num UNION ALL
			SELECT 16 num UNION ALL
			SELECT 17 num UNION ALL
			SELECT 18 num UNION ALL
			SELECT 19 num UNION ALL
			SELECT 20 num UNION ALL
			SELECT 21 num UNION ALL
			SELECT 22 num UNION ALL
			SELECT 23 num UNION ALL
			SELECT 24 num UNION ALL
			SELECT 25 num UNION ALL
			SELECT 26 num UNION ALL
			SELECT 27 num UNION ALL
			SELECT 28 num UNION ALL
			SELECT 29 num UNION ALL
			SELECT 30 num
			) as aa
			) as  tt
			on tt.date=DATE_FORMAT(alarm.createtime,'%Y-%m-%d')
			group by tt.date
			ORDER BY XCLASSIFY   ASC

--最近一周
			select  tt.date as XCLASSIFY,IFNULL(COUNT(alarm.id),0) as COUNT
			from ls_alarm as alarm
			RIGHT JOIN (
			select DATE_SUB(CURDATE(), INTERVAL aa.num DAY) as date from (
			SELECT 0 num UNION ALL
			SELECT 1 num UNION ALL
			SELECT 2 num UNION ALL
			SELECT 3 num UNION ALL
			SELECT 4 num UNION ALL
			SELECT 5 num UNION ALL
			SELECT 6 num
			) as aa
			) as  tt
			on tt.date=DATE_FORMAT(alarm.createtime,'%Y-%m-%d')
			group by tt.date
			ORDER BY XCLASSIFY   ASC

--最近24小时
			select  tt.date as XCLASSIFY,IFNULL(COUNT(alarm.id),0) as COUNT
			from ls_alarm as alarm
			RIGHT JOIN (
			select DATE_FORMAT(DATE_SUB(now(), INTERVAL aa.num HOUR),'%Y-%m-%d %H') as date
			from (
			SELECT 0 num UNION ALL
			SELECT 1 num UNION ALL
			SELECT 2 num UNION ALL
			SELECT 3 num UNION ALL
			SELECT 4 num UNION ALL
			SELECT 5 num UNION ALL
			SELECT 6 num UNION ALL
			SELECT 7 num UNION ALL
			SELECT 8 num UNION ALL
			SELECT 9 num UNION ALL
			SELECT 10 num UNION ALL
			SELECT 11 num UNION ALL
			SELECT 12 num UNION ALL
			SELECT 13 num UNION ALL
			SELECT 14 num UNION ALL
			SELECT 15 num UNION ALL
			SELECT 16 num UNION ALL
			SELECT 17 num UNION ALL
			SELECT 18 num UNION ALL
			SELECT 19 num UNION ALL
			SELECT 20 num UNION ALL
			SELECT 21 num UNION ALL
			SELECT 22 num UNION ALL
			SELECT 23 num UNION ALL
			SELECT 23 num
			) as aa
			) as  tt
			on tt.date=DATE_FORMAT(alarm.createtime,'%Y-%m-%d %H')
			group by tt.date
			ORDER BY XCLASSIFY   ASC