mysql连续输出日期的几种写法

第一种：通过查询关联   UNION  构建数据条数  6*5 = 30  

SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL xc DAY), '%Y-%m-%d') as date
FROM ( 
			SELECT @xi:=@xi+1 as xc from 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 )  xc1, 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,  
			(SELECT @xi:=-1) xc0 
) xcxc  

 

第二种： 通过一张数据较多的表（jc_channel） 和 limit 进行 生成30条 日期

SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL xc DAY), '%Y-%m-%d') as date
FROM ( 
			SELECT @xi:=@xi+1 as xc from 
			(SELECT * from jc_channel limit 30 )  xc1, 
			(SELECT @xi:=-1) xc0 
) xcxc 

第三种： 通过一张数据较多的表（jc_channel） 和 时间截取条数 

SELECT DATE_FORMAT(ADDDATE('2019-09-01',INTERVAL @d DAY),'%Y-%m-%d') AS date ,@d :=@d + 1 day
FROM jc_channel,(SELECT @d := 0) temp
WHERE ADDDATE('2019-09-01',INTERVAL @d DAY) <= DATE_FORMAT('2019-09-30', '%Y-%m-%d')  

第一、二种执行结果：

 

第三种执行结果：

 

查询实例： 通过 left join 实现展示无数据日期 ，ifnull 函数 null修改为0

select IFNULL(a.data_sl,0),b.date from 
(
SELECT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL xc DAY), '%Y-%m-%d') as date
FROM ( 
			SELECT @xi:=@xi+1 as xc from 
			(SELECT * from jc_channel limit 30 )  xc1, 
			(SELECT @xi:=-1) xc0 
) xcxc  ) b
LEFT JOIN
(


--业务查询 start    替换自己的业务查询
select count(*) as data_sl, DATE_FORMAT(c.create_time,  '%Y-%m-%d') data_Date from st_measured c  
group by DATE_FORMAT(create_time,  '%Y-%m-%d')
--业务查询 end 

) a   on b.date = a.data_Date   order by b.date desc 

输出结果：

 

st_measured  表数据：