某酒店客房信息数据及某晚入住信息数据如下: 客房信息表guestroom_tb(room_id-房间号,room_type-房间类型,room_price-房间价格),如下所示: 入住信息表checkin_tb(info_id-信息id.room_id-房间号,user_id-客户id,checkin_time-入住时间,checkout_time-退房时间), 该表存储该晚客户入住信息及后续退房信息;如下所示: 问题:请查询该酒店从6月12日开始连续入住多晚的客户信息? 要求输出:客户id、房间号、房间类型、连续入住天数(按照连续入住天数的升序排序,再按照房间号的升序排序,再按照客户id的降序排序) 示例数据结果如下: 解释:以客户203为例,在2022-06-12入住酒店,在2022-06-14退房, 连续在12日晚、13日晚入住在该酒店,故结果如上; 其他结果同理。
示例1
输入
drop table if exists `guestroom_tb` ;
CREATE TABLE `guestroom_tb` (
`room_id` int(11) NOT NULL,
`room_type` varchar(16) NOT NULL,
`room_price` int(11) NOT NULL,
PRIMARY KEY (`room_id`));
INSERT INTO guestroom_tb VALUES(1001,'商务标准房',165);
INSERT INTO guestroom_tb VALUES(1002,'家庭套房',376);
INSERT INTO guestroom_tb VALUES(1003,'商务单人房',100);
INSERT INTO guestroom_tb VALUES(1004,'商务单人房',100);
INSERT INTO guestroom_tb VALUES(1005,'商务标准房',165);
INSERT INTO guestroom_tb VALUES(1006,'商务单人房',100);
INSERT INTO guestroom_tb VALUES(1007,'商务标准房',165);
INSERT INTO guestroom_tb VALUES(1008,'家庭套房',365);
INSERT INTO guestroom_tb VALUES(1009,'商务标准房',165);
drop table if exists `checkin_tb` ;
CREATE TABLE `checkin_tb` (
`info_id` int(11) NOT NULL,
`room_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`checkin_time` datetime NOT NULL,
`checkout_time` datetime NOT NULL,
PRIMARY KEY (`info_id`));
INSERT INTO checkin_tb VALUES(1,1001,201,'2022-06-12 15:00:00','2022-06-13 09:00:00');
INSERT INTO checkin_tb VALUES(2,1001,202,'2022-06-12 15:00:00','2022-06-13 09:00:00');
INSERT INTO checkin_tb VALUES(3,1003,203,'2022-06-12 14:00:00','2022-06-14 08:00:00');
INSERT INTO checkin_tb VALUES(4,1004,204,'2022-06-12 15:00:00','2022-06-13 11:00:00');
INSERT INTO checkin_tb VALUES(5,1007,205,'2022-06-12 16:00:00','2022-06-15 12:00:00');
INSERT INTO checkin_tb VALUES(6,1008,206,'2022-06-12 19:00:00','2022-06-13 12:00:00');
INSERT INTO checkin_tb VALUES(7,1008,207,'2022-06-12 19:00:00','2022-06-13 12:00:00');
INSERT INTO checkin_tb VALUES(8,1009,208,'2022-06-12 20:00:00','2022-06-16 09:00:00');
输出
user_id|room_id|room_type|days
203|1003|商务单人房|2
205|1007|商务标准房|3
208|1009|商务标准房|4
加载中...