题解 | 查询连续入住多晚的客户信息？

select user_id, a.room_id, room_type, datediff(checkout_time, checkin_time) days
from guestroom_tb a
inner join (
    select info_id, room_id, user_id, substring_index(checkin_time,' ',1) checkin_time, substring_index(checkout_time,' ',1) checkout_time
    from checkin_tb
) b
on a.room_id = b.room_id
where checkin_time = '2022-06-12' and checkout_time > '2022-06-13'
order by days, room_id, user_id desc;

关键在于使用datediff函数求出最大连续入住天数，datediff(X,Y)函数X为大日期，Y为小日期，否则相减得负数。

同时需要注意本题目的业务问题，需要连续入住多晚（入住一晚将不统计在内）。