描述
某酒店客房信息数据及某晚入住信息数据如下:
客房信息表guestroom_tb(room_id-房间号,room_type-房间类型,room_price-房间价格),如下所示:
room_id | room_type | room_price |
---|---|---|
1001 | 商务标准房 | 165 |
1002 | 家庭套房 | 376 |
1003 | 商务单人房 | 100 |
1004 | 商务单人房 | 100 |
1005 | 商务标准房 | 165 |
1006 | 商务单人房 | 100 |
1007 | 商务标准房 | 165 |
1008 | 家庭套房 | 365 |
1009 | 商务标准房 | 165 |
入住信息表checkin_tb(info_id-信息id.room_id-房间号,user_id-客户id,checkin_time-入住时间,checkout_time-退房时间),
该表存储该晚客户入住信息及后续退房信息,如下所示:
info_id | room_id | user_id | checkin_time | checkout_time |
---|---|---|---|---|
1 | 1001 | 201 | 2022-06-12 15:00:00 | 2022-06-13 09:00:00 |
2 | 1001 | 202 | 2022-06-12 15:00:00 | 2022-06-13 09:00:00 |
3 | 1003 | 203 | 2022-06-12 14:00:00 | 2022-06-14 08:00:00 |
4 | 1004 | 204 | 2022-06-12 15:00:00 | 2022-06-13 11:00:00 |
5 | 1007 | 205 | 2022-06-12 16:00:00 | 2022-06-15 12:00:00 |
6 | 1008 | 206 | 2022-06-12 19:00:00 | 2022-06-13 12:00:00 |
7 | 1008 | 207 | 2022-06-12 19:00:00 | 2022-06-13 12:00:00 |
8 | 1009 | 208 | 2022-06-12 20:00:00 | 2022-06-16 09:00:00 |
问题:请查询该酒店从6月12日开始连续入住多晚的客户信息?
要求输出:客户id、房间号、房间类型、连续入住天数(按照连续入住天数的升序排序,再按照房间号的升序排序,再按照客户id的降序排序)
示例数据结果如下:
user_id | room_id | room_type | days |
---|---|---|---|
203 | 1003 | 商务单人房 | 2 |
205 | 1007 | 商务标准房 | 3 |
208 | 1009 | 商务标准房 | 4 |
解释:以客户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
答案
解法1:
select
c.user_id,
c.room_id,
g.room_type,
datediff(c.checkout_time,c.checkin_time) days
from
checkin_tb c
left join guestroom_tb g on c.room_id=g.room_id
where
datediff(c.checkout_time,c.checkin_time)>1
and c.checkin_time >= '2022-6-12'
order by days,room_id,user_id desc;
*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*