显示两个日期之间的日期列表? [英] 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屋！