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

SELECT
  user_id,
  room_id,
  room_type,
  `datediff` days 
FROM
  (
  SELECT
    *,
    ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY DATEDIFF( checkout_time, checkin_time ) DESC ) continue_days_rk 
  FROM
    (
    SELECT
      ct.user_id,
      ct.room_id,
      gr.room_type,
      DATEDIFF( checkout_time, checkin_time ) `datediff`,
      checkout_time,
      checkin_time 
    FROM
      checkin_tb ct
      INNER JOIN guestroom_tb gr ON ct.room_id = gr.room_id 
      AND DATE ( ct.checkin_time )>= '2022-06-12' 
    ) t 
  WHERE
    `datediff` != 1 
  ) continue_day_t 
WHERE
  continue_days_rk = 1 
ORDER BY
  days,
  room_id,
  user_id DESC