在mysql中显示所有日期甚至没有数据

ecfsfe2w 于 2021-06-17 发布在 Mysql

关注(0)|答案(2)|浏览(451)

这个问题在这里已经有答案了：

mysql如何在范围内填充缺失的日期(5个答案）
两年前关门了。
我有个问题，

select value, updateddate from valid_table where date(updateddate) between '2018-11-01' and '2018-11-07';

value date
40    2018-11-01
50    2018-11-03
70    2018-11-05

但我想显示所有日期，即使没有如下特定日期的数据，

value date
40    2018-11-01
0     2018-11-02
50    2018-11-03
0     2018-11-04
70    2018-11-05
0     2018-11-06
0     2018-11-07

注：起止日期为2018-10-01和2018-12-01
如何做到这一点？

sql mysql

来源：https://stackoverflow.com/questions/53928284/display-all-dates-even-no-data-in-mysql

2条答案

按热度按时间

h5qlskok1#

你可以在下面使用左连接

select tdate, coalesce(value,0) as value from 
    (select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) tdate from
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
     (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) t1
    left join valid_table on t1.tdate= updateddate 
    where tdate between '2012-02-10' and '2012-02-15'

赞(0）回复(0）举报 2021-06-18

xe55xuns2#

要获取开始日期和结束日期之间的日期，可以使用下面的查询。希望这能对你有所帮助。要从表中得到这个结果，可以使用联接。

select * from 
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2012-02-10' and '2012-02-15'

赞(0）回复(0）举报 2021-06-17

我来回答

在mysql中显示所有日期甚至没有数据

2条答案

相关问题

热门标签

最新问答