使用 MySQL 查询选择房间可用性

问题描述

我在酒店预订中使用了这个关于房间可用性的数据库结构

I had this database structure in a hotel reservation in terms of room availability

下面是我的房间

room_num    roomtype     
101         Single    
102         Single    
103         Single    
111         Deluxe    
112         Deluxe   
113         Deluxe    
114         Deluxe    
115         Deluxe    
116         Deluxe    
121         Superior  
122         Superior  

下面的表格是room_booked，记录了预定的房间号

and the table below is room_booked which is records booked room numbers

dor 是入住日期，dco 是退房日期

dor is checkin date and dco is date checkout

room_num        dor         dco
  111        2014-06-01  2014-06-06
  112        2014-06-01  2014-06-06
  113        2014-06-01  2014-06-06
  114        2014-06-01  2014-06-06
  115        2014-06-01  2014-06-06
  116        2014-06-01  2014-06-06
  112        2014-05-18  2014-05-21
  113        2014-08-01  2014-08-04

在这种情况下，所有豪华房都是从 2014-06-01 到 2014-06-06 预订的.然后我使用下面的MySQL代码选择了2014-06-01至2014-06-06可用的豪华房

In this case, all of Deluxe room are booked from 2014-06-01 to 2014-06-06. Then I selected Deluxe room available from 2014-06-01 to 2014-06-06 by using this MySQL code below

SELECT
    room.room_num
FROM
    room
LEFT JOIN
    room_booked ON room_booked.room_num = room.room_num
WHERE
    (
        (
            dor IS NULL AND dco IS NULL
        )
        OR (
            -- wished booking date is before DOR
            '2014-06-06' < dor
            -- OR wished booking date is after DCO
            OR '2014-06-01' >=  dco
        )
    )
    -- room type
    AND roomtype = 'Deluxe'

但是它显示了豪华房号.112 和 113 在上述选定日期范围之前和之后被预订，而不是 0 或已满.有没有办法修复这个代码?

However it shows Deluxe room no. 112 and 113 which is booked before and after selected date range above instead of 0 or fully booked. Is there any solution to fix this code?

推荐答案

我认为您必须检查入住和退房日期:

I think you have to check both dates for check in and check out:

SELECT
    room.room_num
FROM
    room
WHERE
  roomtype = 'Deluxe' AND
  room.room_num not in 
  (
    SELECT
      room_booked.room_num
    FROM
      room_booked
    WHERE
      (room_booked.dor<='2014-06-1' and room_booked.dco>='2014-06-1')
      OR
      (room_booked.dor<'2014-06-06' and room_booked.dco>='2014-06-06')
      OR
      (room_booked.dor>='2014-06-01' and room_booked.dco<'2014-06-06')
   )

重新编辑答案并创建了一个 sqlfiddle 用于测试

re-Edited answer and created a sqlfiddle for testing

这篇关于使用 MySQL 查询选择房间可用性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！