订房SQL查询 [英] Room booking sql query

问题描述

我在编写sql以从表中获取可用房间时遇到问题.我的表格结构如下.

I have a problem in writing the sql to get the available rooms from the tables. my table structures are given below.

表:预订

booking_id    |    room_id   |    start_datetime  |   end_datetime    |   customer_id
-------------------------------------------------------------------------------------
001           |     1        |  12-09-2012 2:35pm | 14-09-2012 9:00am |    23
002           |     2        | 10-09-2012 1:00am  | 20-09-2012 9:00am |    20
003           |     4        |  09-09-2012 1:35pm | 21-09-2012 9:00am |    43
004           |     1        |  22-09-2012 2:35pm | 24-09-2012 9:00am |    9
005           |     3        |  12-09-2012 9:00am | 13-09-2012 9:00am |    53
006           |     6        |  15-09-2012 9:00am | 19-09-2012 9:00am |    27

桌子:房间

包含有关房间的详细信息，该表的主键是room_id，从10到10共有10个房间.

contains the details about the rooms and the primary key of the table is room_id and it has 10 rooms from 1-10.

我的问题是我想知道这些房间在2012年14月9日6:00 pm至21-09-2012 9:00 am之间可用，这意味着我只能得到room_id的结果为1,3,5， 7,8,9,10.

My problem is I want to know the rooms are available between 14-09-2012 6:00pm to 21-09-2012 9:00am which means I should only get the results of the room_id's as 1,3,5,7,8,9,10.

有人可以帮我编写SQL来从上述表结构中获取可用的房间吗? 我正在使用mysql作为数据库引擎. 预先感谢.

Can someone help me to write the SQL to get the available rooms from the above table structures. I'm using mysql as the database engine. Thanks in advance.

推荐答案

这应该可以做到； 如果某个预订没有在我们想要的预订之前结束或之后开始，则该房间被视为忙碌.

This should do it; if there is a reservation that does not end before or start after the reservation we want, the room is considered busy.

SELECT r.room_id
FROM rooms r
WHERE r.room_id NOT IN (
    SELECT b.room_id FROM bookings b
    WHERE NOT (b.end_datetime   < '2012-09-14T18:00'
               OR
               b.start_datetime > '2012-09-21T09:00'))
ORDER BY r.room_id;

此处的SQLFiddle .

这篇关于订房SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！