显示两个日期之间的日期列表? [英] Show a list of dates between two dates?
本文介绍了显示两个日期之间的日期列表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何显示介于 start_date
和 end_date
之间的日期列表?
How to show a list of date between between start_date
and end_date
?
我尝试使用 group by group by DAY(start_date),DAY(end_date)
但没有成功.
I have tried using group by group by DAY(start_date),DAY(end_date)
but no success.
例如,在预订表中它看起来像这样:
For example, in the booking table it look like this:
+----+-----------+--------+---------------------+---------------------+----------+
| id | client_id | car_id | start_date | end_date | status |
+----+-----------+--------+---------------------+---------------------+----------+
| 1 | 1 | 2 | 2015-10-03 00:00:00 | 2015-10-05 00:00:00 | Reserved |
| 2 | 3 | 5 | 2015-10-15 00:00:00 | 2015-10-17 00:00:00 | Hired |
+----+-----------+--------+---------------------+---------------------+----------+
我希望结果是这样的:
client_id car_id booked_date status
1 2 2015-10-03 Reserved
1 2 2015-10-04 Reserved
1 2 2015-10-05 Reserved
3 5 2015-10-15 Hired
3 5 2015-10-16 Hired
3 5 2015-10-17 Hired
推荐答案
创建表调用方:
CREATE TABLE IF NOT EXISTS `callender` (
`date` date NOT NULL,
PRIMARY KEY (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
插入日期:
INSERT INTO `callender` (`date`) VALUES
('2015-10-03'),
('2015-10-04'),
('2015-10-05'),
('2015-10-06'),
('2015-10-07'),
('2015-10-08'),
('2015-10-09'),
('2015-10-10'),
('2015-10-11'),
('2015-10-12'),
('2015-10-13'),
('2015-10-14'),
('2015-10-15'),
('2015-10-16'),
('2015-10-17'),
('2015-10-18');
运行查询:
SELECT client_id, car_id, date, status
FROM booking
LEFT JOIN callender ON (date >= start_date
AND date<=end_date)
这篇关于显示两个日期之间的日期列表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文