mysql - 查询房间入住情况时获得额外天数

Question

查询房间入住情况时，我得到了额外的天数。我认为这是因为即使在有两次预订的日子里，它仍然会执行 SUM(DATEDIFF(...) +1) 。例如，在 2 月份，它会返回房间的 31 天。

如何添加一天(除了有两次预订的日期之外)，然后只需为两者之一添加 1 即可。

SELECT 
  room_id, 
  SUM(DATEDIFF(
    LEAST(end_time, '2019-12-31 23:59:59'),
    GREATEST(start_time, '2019-12-01 00:00:00')
  ) + 1) as days 
FROM reservations 
WHERE 
  (start_time >= '2019-12-01 00:00:00' AND start_time < '2020-01-01 00:00:00')
  OR
  (end_time >= '2019-12-01 00:00:00' AND end_time < '2020-01-01 00:00:00')
GROUP BY room_id

这是一个 fiddle ，代表不需要的额外一天， https://www.db-fiddle.com/f/ta53CprLisnRXh9A8wVwZv/1返回 7 天，但房间已保留 5 天

Answer 1

您可以使用 calendar table解决这个问题:

SELECT 
  room_id, 
  COUNT(DISTINCT date_value)
FROM test INNER JOIN (
  SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS date_value
  FROM
    (SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0,
    (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
    (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
    (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3,
    (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4
  ) calendar ON calendar.date_value BETWEEN CAST(start_time AS DATE) AND CAST(end_time AS DATE)
WHERE 
  (start_time >= '2019-02-01 00:00:00' AND start_time < '2019-03-01 00:00:00')
  OR
  (end_time >= '2019-02-01 00:00:00' AND end_time < '2019-03-01 00:00:00')
GROUP BY room_id;

日历表是一个生成表，以 1970-01-01 以来的所有日期作为日期值。您可以将表连接到日历表并计算与表匹配的所有不同日期。

demo