mysql - MySQL - 选择所有房间,包括部分日期范围
问题描述
我目前有可用的房间,它显示给定日期范围的当前开放房间预订。
我需要显示相同的可用性,但我需要显示部分可用性,而不是显示完全可用性的房间。
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
解决方案
忽略令人痛苦的命名政策......
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 |
+----+--------+----------+---------+
推荐阅读
- c++ - MSTSCLib::MsRdpClient 行为不正确
- javascript - 如何从我的方法函数中获取对象值或函数?
- woocommerce - 如何让用户在购买woocommerce之前填写他们的帐户信息
- angular - 关于将 OpenLayers 模块导入 Angular 2+ 的问题
- javascript - Javascript 对象中的函数数组
- excel - 有没有办法将水平数据导入 Excel 列?
- javascript - 删除数组中每个对象的名字/值
- r - dplyr::summarize 按字母顺序排列,但我需要原始顺序
- html - 使用 Safari 应用 SVG 剪辑路径时遇到问题
- game-engine - 搅拌机从位置获取对象