我有一張表格：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。但是我得到了第一個表格格式而不是第二個表格格式。
4 回答
海綿寶寶撒
TA貢獻1809條經驗 獲得超8個贊
首先，您可以準備兩個指定日期之間的所有日期。參考這個答案，子查詢將是：
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'
我不確定是否有更簡單的答案，但這對我有用。
上面的鏈接解釋了子查詢如何獲取兩個給定日期之間的所有日期。希望這可以幫助！
當年話下
TA貢獻1890條經驗 獲得超9個贊
最容易你可以像這樣使用：
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
德瑪西亞99
TA貢獻1770條經驗 獲得超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
牛魔王的故事
TA貢獻1830條經驗 獲得超3個贊
SELECT * FROM booking
WHERE date >= '2020-01-01 00:00:00'
AND date <= '2020-05-01 00:00:00'
- 4 回答
- 0 關注
- 252 瀏覽
添加回答
舉報
0/150
提交
取消