显示两个日期之间的所有日期

Question

我有一张表格：

date       hrs

01-01-2020  5

03-01-2020  1

05-01-2020  2

当我在 php 中使用 mysql 命令时，我想获取从 01-01-2020 到 05-01-2020 的所有日期作为输出：

date       hrs

01-01-2020  5

02-01-2020  0

03-01-2020  1

04-01-2020  0

05-01-2020  2

我使用了以下语句：

SELECT dates FROM booking WHERE date BETWEEN '$start' AND '$end'"

其中开始日期是 01-01-2020，结束日期是 05-01-2020。但是我得到了第一个表格格式而不是第二个表格格式。

Answer 1

首先，您可以准备两个指定日期之间的所有日期。参考这个答案，子查询将是：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) vwhere selected_date between '$start' AND '$end'然后我将把这个表加入到你拥有的表中，如下所示：select v.selected_date , coalesce(booking.hrs, 0) 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) vleft join booking on v.selected_date = booking.datewhere v.selected_date between '$start' AND '$end'我不确定是否有更简单的答案，但这对我有用。上面的链接解释了子查询如何获取两个给定日期之间的所有日期。希望这可以帮助！

Answer 2

最容易你可以像这样使用：SET @date_min = '2020-01-01';SET @date_max = '2020-01-05';SELECT   date_generator.date as dates,   IFNULL(hrs, 0) as hrsfrom (   select DATE_ADD(@date_min, INTERVAL (@i:=@i+1)-1 DAY) as `date`   from information_schema.columns,(SELECT @i:=0) gen_sub    where DATE_ADD(@date_min,INTERVAL @i DAY) BETWEEN @date_min AND @date_max) date_generatorleft join booking on DATE(`date`) = date_generator.dateGROUP BY `date`;所以在这里我正在创建一个临时表 date_generator 将日期介于给定日期范围之间，并与您的主表（事务）连接。按预期输出：dates      | hrs 01-01-2020 | 502-01-2020 | 003-01-2020 | 104-01-2020 | 005-01-2020 | 2

Answer 3

如果您的 MySQL 服务器版本是 8+，请使用WITH RECURSIVE&nbsp;cte AS ( SELECT MIN(dates) dates&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM booking&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;UNION ALL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;SELECT dates + INTERVAL 1 DAY&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM cte&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE dates < ( SELECT MAX(dates)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM booking ) )SELECT dates, hrsFROM bookingUNION ALLSELECT dates, 0FROM cteWHERE NOT EXISTS ( SELECT NULL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FROM booking&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;WHERE booking.dates = cte.dates )ORDER BY dates

Answer 4

SELECT * FROM booking&nbsp;WHERE date >= '2020-01-01 00:00:00'&nbsp;AND date <= '2020-05-01 00:00:00'