我有两个数据库表,一个存储属性,另一个存储这些属性的预订。以下是表格结构的简化版本:
property
+-------------------+--------------+
| Field | Type |
+-------------------+--------------+
| id | int(11) |
| status | int(1) |
| title | varchar(150) |
+-------------------+--------------+
booking
+-------------------+--------------+
| Field | Type |
+-------------------+--------------+
| id | int(11) |
| status | int(1) |
| property_id | int(11) |
| start_date | date |
| end_date | date |
+-------------------+--------------+
我有一个搜索表,上面有开始和结束日期,可以查看在给定的时间段内有哪些房产可用。例如"2012-12-01至2012-12-07期间有哪些房产可供出租"。
我有以下SQL,它在一定程度上起作用:
SELECT p.id, p.title FROM property p WHERE p.status=1 AND p.id NOT IN (SELECT
b.property_id FROM booking b WHERE (b.status=1 OR b.status=2 OR b.status=3 OR
b.status=6) AND NOT (b.enddate < '2013-05-30' OR b.startdate > '2013-05-24'))
ORDER BY p.title ASC
问题是,它无法处理日期的重叠。如果搜索的是现有预订中的日期范围,则SQL运行良好,不会返回该属性。然而,如果搜索日期与预订日期重叠,即使在搜索期的一部分时间内预订了房产,仍会被退回。
我在这里设置了一个SQL fiddle:http://sqlfiddle.com/#!2/d02e1/4-有5个查询-前2个工作正常。我需要返回的属性如下:
- 查询1(工作)-1、2和3
- 查询2(工作)-2和3
- 查询3(不工作)-2和3
- 查询4(不工作)-2和3
- 查询5(不工作)-2和3
非常感谢任何帮助或建议。
更新;
我想我的措辞可能有点不清楚。我想做的是获得一份在给定日期范围(搜索日期)内可供出租的房产列表。如果:;搜索日期在现有预订日期范围内,或者在两端重叠。如果在搜索日期之间的任何时间段内都没有预订,则该房产可用。
需要这样的东西吗?:
SELECT p.id, p.title
FROM property p
WHERE p.status=1 AND p.id NOT IN (
SELECT b.property_id
FROM booking b
WHERE (b.status IN (1, 2, 3, 6))
AND (b.end_date BETWEEN '2012-12-01' AND '2012-12-30'
OR b.start_date BETWEEN '2012-12-01' AND '2012-12-30')
)
ORDER BY p.title ASC;
它返回2和3
在这里设法解决了这个问题:http://sqlfiddle.com/#!2/d02e1/64