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

Question

我有一张表格：date       hrs01-01-2020  503-01-2020  105-01-2020  2当我在 php 中使用 mysql 命令时，我想获取从 01-01-2020 到 05-01-2020 的所有日期作为输出：date       hrs01-01-2020  502-01-2020  003-01-2020  104-01-2020  005-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) v

where 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) v

left join booking on v.selected_date = booking.date

where 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 hrs

from (

   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_generator

left join booking on DATE(`date`) = date_generator.date

GROUP BY `date`;

所以在这里我正在创建一个临时表 date_generator 将日期介于给定日期范围之间，并与您的主表（事务）连接。

按预期输出：

dates      | hrs 

01-01-2020 | 5

02-01-2020 | 0

03-01-2020 | 1

04-01-2020 | 0

05-01-2020 | 2

Answer 3

如果您的 MySQL 服务器版本是 8+，请使用

WITH RECURSIVE 

cte AS ( SELECT MIN(dates) dates

         FROM booking 

         UNION ALL

         SELECT dates + INTERVAL 1 DAY

         FROM cte

         WHERE dates < ( SELECT MAX(dates)

                         FROM booking ) )

SELECT dates, hrs

FROM booking

UNION ALL

SELECT dates, 0

FROM cte

WHERE NOT EXISTS ( SELECT NULL

                   FROM booking

                   WHERE booking.dates = cte.dates )

ORDER BY dates

Answer 4

SELECT * FROM booking 

WHERE date >= '2020-01-01 00:00:00' 

AND date <= '2020-05-01 00:00:00'