我有这样的表,
Mst_Booking
room_id int(11),
order_id int(11),
start datettime,
end datetime
我可以获取此查询预订的特定日期
select * from Mst_Booking etc..,
结果
1 1 2017-01-01 2017-01-08
1 1 2017-01-11 2017-01-12
但我无法获得未在此处列出的数据喜欢这个
"未预订/可用日期"
room_id start end
1 2017-01-09 2017-01-10
1 2017-01-13 2017-01-31
请帮助我创建更多表格来标记未预订的吗? 或者有查询可以根据我的要求检索数据?
如果视图不打扰您,我们可以列出每个room_id下一个的"未预订"日期>>
您的餐桌:
mysql> select * from mst_booking;
+---------+----------+---------------------+---------------------+
| room_id | order_id | start_date | end_date |
+---------+----------+---------------------+---------------------+
| 1 | 1 | 2017-01-01 00:00:00 | 2017-01-08 00:00:00 |
| 1 | 2 | 2017-01-11 00:00:00 | 2017-01-12 00:00:00 |
| 1 | 3 | 2017-01-17 00:00:00 | 2017-01-21 00:00:00 |
| 1 | 4 | 2017-01-25 00:00:00 | 2017-01-27 00:00:00 |
| 2 | 5 | 2017-01-17 00:00:00 | 2017-01-21 00:00:00 |
| 2 | 6 | 2017-01-25 00:00:00 | 2017-01-27 00:00:00 |
| 3 | 7 | 2017-01-16 00:00:00 | 2017-01-19 00:00:00 |
| 3 | 8 | 2017-01-25 00:00:00 | 2017-01-29 00:00:00 |
+---------+----------+---------------------+---------------------+
要列出"未预订"日期,请执行以下操作:
select room_id, adddate(end_date,1) as date from mst_booking where
room_id=1 UNION select room_id,subdate(start_date,1) as date from
mst_booking where start_date not in (select min(start_date) from
mst_booking where room_id=1) and room_id=1 order by date;
结果:
+---------+---------------------+
| room_id | date |
+---------+---------------------+
| 1 | 2017-01-09 00:00:00 |
| 1 | 2017-01-10 00:00:00 |
| 1 | 2017-01-13 00:00:00 |
| 1 | 2017-01-16 00:00:00 |
| 1 | 2017-01-22 00:00:00 |
| 1 | 2017-01-24 00:00:00 |
| 1 | 2017-01-28 00:00:00 |
+---------+---------------------+
您可以看到"未预订/有空"日期范围列在另一个下方。
对于 room_id=2:
select room_id, adddate(end_date,1) as date from mst_booking where
room_id=2 UNION select room_id,subdate(start_date,1) as date from
mst_booking where start_date not in (select min(start_date) from
mst_booking where room_id=2) and room_id=2 order by date;
结果:
+---------+---------------------+
| room_id | date |
+---------+---------------------+
| 2 | 2017-01-22 00:00:00 |
| 2 | 2017-01-24 00:00:00 |
| 2 | 2017-01-28 00:00:00 |
+---------+---------------------+
对于 room_id=3:
select room_id, adddate(end_date,1) as date from mst_booking where
room_id=3 UNION select room_id,subdate(start_date,1) as date from
mst_booking where start_date not in (select min(start_date) from
mst_booking where room_id=3) and room_id=3 order by date;
结果:
+---------+---------------------+
| room_id | date |
+---------+---------------------+
| 3 | 2017-01-20 00:00:00 |
| 3 | 2017-01-24 00:00:00 |
| 3 | 2017-01-30 00:00:00 |
+---------+---------------------+