MySQL 根据开始日期和结束日期之间的日期显示值

Question

我正在尝试制作简单的房屋预订系统，我的数据库如下:

TABLE HOUSES
id | name
============
1  | house_1
2  | house_2

TABLE RESERVATIONS
id | id_house | date_start | date_end
=====================================
1  | 2        | 2015-11-02 | 2015-11-10
2  | 1        | 2015-10-02 | 2015-10-15

而且我不知道如何显示基于新预订的预订房屋，例如。

id_house 2 2015-11-05 to 2015-11-20

系统应该输出:House num。 2这天保留

Answer 1

考虑以下几点:

CREATE TABLE reservations
(id INT NOT NULL
,id_house INT NOT NULL
,date_start DATE
,date_end DATE
);

INSERT INTO reservations VALUES
(1,2,'2015-11-02','2015-11-10'),
(2,1,'2015-10-02','2015-10-15');

SELECT * 
  FROM reservations x 
  LEFT 
  JOIN (SELECT '2015-11-05' my_start,'2015-11-20' my_end) y 
    ON y.my_start < x.date_end 
   AND y.my_end > x.date_start;
+----+----------+------------+------------+------------+------------+
| id | id_house | date_start | date_end   | my_start   | my_end     |
+----+----------+------------+------------+------------+------------+
|  1 |        2 | 2015-11-02 | 2015-11-10 | 2015-11-05 | 2015-11-20 |
|  2 |        1 | 2015-10-02 | 2015-10-15 | NULL       | NULL       |
+----+----------+------------+------------+------------+------------+