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

select
        *
from (
    select
        a.user_id,
        a.room_id,
        gr_tb.room_type,
        datediff(checkout_time,checkin_time) days
    from(
        select
            info_id,
            room_id,
            user_id,
            substr(checkin_time,1,10) checkin_time,
            substr(checkout_time,1,10) checkout_time
        from 
                checkin_tb
        where
                substr(checkin_time,6,5) >= '06-12'
    )a
    left join 
            guestroom_tb as gr_tb
    on 
            a.room_id = gr_tb.room_id
    where 
            datediff(checkout_time,checkin_time) > 1
)b
order by 
        days,
        room_id,
        user_id desc