MySQL-选择所有房间，包括部分日期范围

Question

Im当前有房间可用性，显示指定日期范围内的当前开放预订房间。

我需要显示相同的可用性，但我需要显示部分可用性，而不是显示全部可用性的房间。

Eg: booking 1 is from dates 22nd to 25th (within room 4)
booking 2 is from dates 24th to 28th (within room 3)
queried booking is from 23rd till 25th
22nd    23rd  24th     25th      28th
|-----------------------|
|------------------|
|------|                    free space

查询：

SELECT r.*
, CASE WHEN b.ref IS NULL THEN 'all' ELSE 'partial' END status
FROM roominfo r 
LEFT JOIN bookroom br ON br.id = r.id 
LEFT JOIN book b ON b.ref = br.ref 
AND b.end_date >= '2019-11-23' AND b.start_date <= '2019-11-25'
ORDERBY r.id;         

示例结构&数据：

CREATE SCHEMA TEST;
USE TEST;
CREATE TABLE BOOK( Ref INT NOT NULL AUTO_INCREMENT, Start_Date DATE NOT NULL, End_Date DATE NOT NULL, PRIMARY KEY(Ref));
CREATE TABLE ROOMINFO( ID INT NOT NULL AUTO_INCREMENT, `Type` VARCHAR(10) NOT NULL, Max TINYINT NOT NULL, PRIMARY KEY(ID));
CREATE TABLE BOOKROOM( Ref INT NOT NULL,ID INT NOT NULL, FOREIGN KEY (Ref) REFERENCES BOOK(Ref), FOREIGN KEY (ID) REFERENCES ROOMINFO(ID));
INSERT INTO BOOK(Start_Date, End_Date) VALUES   
('2019-11-22', '2019-11-25'),('2019-11-24', '2019-11-28'),('2019-12-01', '2019-12-02'),('2019-12-01', '2019-12-06'),
('2019-12-02', '2019-12-03'),('2019-12-04', '2019-12-10'),('2019-12-04', '2019-12-10'),('2019-12-05', '2019-12-13'),
('2019-12-16', '2019-12-19'),('2019-12-26', '2019-12-28'),('2019-12-26', '2020-01-01'),('2019-12-28', '2020-01-02'),
('2019-12-31', '2020-01-05'),('2020-01-03', '2020-01-08'),('2020-01-05', '2020-01-11'),('2020-01-06', '2020-01-09'),
('2020-01-06', '2020-01-11'),('2020-01-08', '2020-01-18'),('2020-01-11', '2020-01-15'),('2020-01-15', '2020-01-17'),
('2020-01-15', '2020-01-18');
INSERT INTO ROOMINFO (ID, `Type`,Max) VALUES
(1, "Family", 4), (2, "Family", 4), (3, "Family", 4), (4, "Dual", 2),
(5, "Dual", 2),   (6, "Dual", 2), (7, "Dual", 2),   (8, "Dual", 2),
(9, "Dual", 2),   (10, "Dual", 2);
INSERT INTO BOOKROOM( Ref, ID ) VALUES
(1, 4), (2, 3), (3, 4), (4, 5),(5, 6), (6, 7), (7, 3), (8, 2), (9, 1), (10, 8),(11, 3), 
(12, 9), (13, 2), (14, 10), (15, 4), (16, 5), (17, 6), (18, 7), (19, 2),(20, 1), (21, 10);

期望输出：

id    (& some indication of partial availability?)
1  all
2  all
3  partial
4  partial
5  all
6  all
7  all
8  all
9  all
10  all

Answer 1

忽略令人痛苦的命名策略。。。

DROP TABLE IF EXISTS book;
CREATE TABLE BOOK( Ref INT NOT NULL AUTO_INCREMENT, Start_Date DATE NOT NULL, End_Date DATE NOT NULL, PRIMARY KEY(Ref));
DROP TABLE IF EXISTS roominfo;
CREATE TABLE ROOMINFO( ID INT NOT NULL AUTO_INCREMENT, `Type` VARCHAR(10) NOT NULL, capacity TINYINT NOT NULL, PRIMARY KEY(ID));
DROP TABLE IF EXISTS bookroom;
CREATE TABLE BOOKROOM( Ref INT NOT NULL,ID INT NOT NULL);
INSERT INTO BOOK(Start_Date, End_Date) VALUES   
("2019-11-03", "2019-11-10"), ("2019-11-05", "2019-11-13");
INSERT INTO ROOMINFO (ID, `Type`,capacity) VALUES
(1, "Family", 4), (2, "Family", 4), (3, "Family", 4), (4, "Dual", 2),
(5, "Dual", 2),   (6, "Dual", 2), (7, "Dual", 2),   (8, "Dual", 2),
(9, "Dual", 2),   (10, "Dual", 2);
INSERT INTO BOOKROOM( Ref, ID ) VALUES (1, 4), (2, 3);
SELECT r.*
, CASE WHEN b.ref IS NULL THEN 'all' ELSE 'partial' END status
FROM roominfo r 
LEFT 
JOIN bookroom br 
ON br.id = r.id 
LEFT 
JOIN book b 
ON b.ref = br.ref 
AND b.end_date >= '2019-11-01' AND b.start_date <= '2019-11-13'
ORDER
BY r.id;
+----+--------+----------+---------+
| ID | Type   | capacity | status  |
+----+--------+----------+---------+
|  1 | Family |        4 | all     |
|  2 | Family |        4 | all     |
|  3 | Family |        4 | partial |
|  4 | Dual   |        2 | partial |
|  5 | Dual   |        2 | all     |
|  6 | Dual   |        2 | all     |
|  7 | Dual   |        2 | all     |
|  8 | Dual   |        2 | all     |
|  9 | Dual   |        2 | all     |
| 10 | Dual   |        2 | all     |
+----+--------+----------+---------+