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

with tmp as(
select t1.user_id,t1.continue_day,t2.room_id
from(
select user_id,datediff(checkout_time,checkin_time) continue_day
from checkin_tb
where checkin_time>='2022-06-12'
group by user_id,continue_day
) t1,checkin_tb t2
where t1.continue_day>1 and t1.user_id=t2.user_id
)
select tmp.user_id,tmp.room_id,t3.room_type,tmp.continue_day days
from tmp,guestroom_tb t3
where tmp.room_id=t3.room_id
order by days asc,room_id asc,user_id desc

主要就是日期的计算函数datediff，懂了这个，就很简单