回忆是一座桥
却是通往寂寞的牢

SQL案例 - 查询连续入住多晚的客户信息

描述

某酒店客房信息数据及某晚入住信息数据如下:

客房信息表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;

*本案例来自牛客网,但答案为原创,如有雷同纯属巧合*

未经允许不得转载:夕枫 » SQL案例 - 查询连续入住多晚的客户信息
订阅评论
提醒
guest
0 评论
内联反馈
查看所有评论