连续日期

1.创建数据表并添加数据

CREATE TABLE num (i int);  
INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);  

连续日期
select adddate('2012-09-01', 
numlist.id) as `date` from (SELECT n1.i + n10.i*10 + n100.i*100 AS id FROM num n1 cross join num as n10 cross join num as n100) as numlist where adddate('2012-09-01', 
numlist.id) <= '2012-09-10'


连续月份
SELECT LEFT(a.date,7) monthlist FROM (SELECT ADDDATE('2012-09-01', 
numlist.id) AS `date` FROM (SELECT n1.i + n10.i*10 + n100.i*100 AS id FROM num n1 CROSS JOIN 
num AS n10 CROSS JOIN num AS n100) AS numlist WHERE ADDDATE('2012-09-01', 
numlist.id) <= '2017-09-10')a GROUP BY LEFT(a.date,7)

连续小时
SELECT b.time_a FROM (SELECT CASE WHEN a.id<=9 THEN CONCAT('0','',a.id) ELSE a.id END time_a,CASE WHEN a.id>=7 AND a.id<=23 THEN 1 ELSE 2 END time_b FROM (SELECT
n1.i + n10.i * 10 AS id
FROM
num n1
CROSS JOIN num AS n10)a WHERE a.id+0<=23)b
ORDER BY b.time_b+0 ASC,time_a+0 ASC

最近24小时
CREATE or REPLACE view v_recent_h24 as
select CONCAT(a.date,' ',b.time_a,':00:00')date from (select adddate(SUBDATE(CURDATE(),INTERVAL 1 day), 
numlist.id) as `date` from (SELECT n1.i + n10.i*10 + n100.i*100 AS id FROM num n1 cross join num as n10 cross join num as n100) as numlist where adddate(SUBDATE(CURDATE(),INTERVAL 1 day), 
numlist.id) <= CURDATE())a,v_weather_hour b 
where CONCAT(a.date,' ',b.time_a,':00:00')>=SUBDATE(NOW(),INTERVAL 24 hour)
and CONCAT(a.date,' ',b.time_a,':00:00')<=NOW()
order by CONCAT(a.date,' ',b.time_a,':00:00')asc

早8晚18

CREATE or REPLACE view v_h8_18 as
select CONCAT(a.date,' ',b.time_a,':00:00')date from (select CURDATE() date)a,v_weather_hour b
where CONCAT(a.date,' ',b.time_a,':00:00')>=CONCAT(CURDATE(),' 08:00:00')
and CONCAT(a.date,' ',b.time_a,':00:00')<=CONCAT(CURDATE(),' 18:00:00')
order by CONCAT(a.date,' ',b.time_a,':00:00')asc