MySQL - 查看不在預訂範圍內的選擇房間

我在視圖中使用選擇查詢來查找房間何時可用，預訂由參考和開始/結束日期標識。

看法：

USE CATTERY2;
CREATE VIEW RoomAvalability
AS
SELECT ROOMINFO.ID,# ROOMINFO.`Type`, ROOMINFO.Max,
   #GROUP_CONCAT(DISTINCT BOOKROOM.Ref) as Bookings,
   GROUP_CONCAT(DISTINCT BOOK.Ref) as BookRef, ##
   GROUP_CONCAT(DISTINCT BOOK.Start_Date) as StartDate,
   GROUP_CONCAT(DISTINCT BOOK.End_Date) as EndDate
FROM ROOMINFO
LEFT JOIN BOOKROOM ON ROOMINFO.ID = BOOKROOM.ID
INNER JOIN BOOK ON ROOMINFO.ID AND BOOK.Ref = BOOKROOM.Ref ##
GROUP BY ROOMINFO.ID, ROOMINFO.`Type`, ROOMINFO.Max;

在目前記錄的預訂中，一個房間可以有多個預訂（不重疊）。

我目前遇到的問題是指定日期範圍的選擇查詢，我無法繞過它。

我想從查詢中返回在指定日期範圍內沒有預訂的房間列表。

我目前正在嘗試以下 3 種選擇進行測試：

#1
SELECT * FROM RoomAvalability;
#2
SELECT ID, `Type`, Max FROM RoomAvalability WHERE NOT StartDate <= '2019-11-22' AND EndDate <= '2020-11-28';
#3
SELECT ID, `Type`, Max FROM RoomAvalability WHERE StartDate <= '2019-11-22' AND EndDate <= '2020-11-28';

1 返回所有房間

2 還返回所有房間

3 返回無房

1 & 2 結果：

ID, type max
1   2   4
2   2   4
3   2   4
4   1   2
5   1   2
6   1   2
7   1   2
8   1   2
9   1   2
10  1   2

2號不應該返回8個房間而不是10個房間，因為它是3號的負數嗎？

數字 3 沒有提供結果，這意味著在此範圍內沒有預訂，但它應該提供 2 個結果？

我在想的另一件事：我應該從預訂開始並連結房間嗎？

結構和價值觀

CREATE SCHEMA CATTERY2;
USE CATTERY2;
#BOOK
CREATE TABLE BOOK(
          Ref INT NOT NULL AUTO_INCREMENT,                    
          Start_Date DATE NOT NULL,
          End_Date DATE NOT NULL,
          PRIMARY KEY(Ref));
#ROOM
CREATE TABLE ROOMINFO(
         ID INT NOT NULL AUTO_INCREMENT,                       
         `Type` VARCHAR(10) NOT NULL,
         Max TINYINT NOT NULL,
         PRIMARY KEY(ID));
#BOOKROOM
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);

試試這個：

SELECT r.id
FROM ROOMINFO AS r
WHERE NOT EXISTS (SELECT 1 
                   FROM BOOKROOM br
                   JOIN BOOK b ON br.ref = b.ref
                   WHERE r.id = br.id
                       AND ('2019-11-22' BETWEEN b.Start_Date AND b.End_Date
                           OR '2019-11-28' BETWEEN b.Start_Date AND b.End_Date
                           OR b.Start_Date BETWEEN '2019-11-22' AND '2019-11-28'
                           OR b.End_Date BETWEEN '2019-11-22' AND '2019-11-28'
                           )
                 );

或者，如果您將房間 ID 添加到 BOOK 表，您可以使用這樣的查詢：

SELECT r.id
FROM ROOMINFO AS r
WHERE NOT EXISTS (SELECT 1 
                   FROM BOOK 
                   WHERE r.id = id
                       AND ('2019-11-22' BETWEEN Start_Date AND End_Date
                           OR '2019-11-28' BETWEEN Start_Date AND End_Date
                           OR Start_Date BETWEEN '2019-11-22' AND '2019-11-28'
                           OR End_Date BETWEEN '2019-11-22' AND '2019-11-28'
                           )
               );

在這種情況下，您可以創建支持索引 ON BOOK(id, Start_Date, End_Date)

引用自：https://dba.stackexchange.com/questions/265120