MySQL在预订系统中寻找免费房间 [英] MySQL Looking for free rooms in reservation system

问题描述

我在预订系统中找到免费房间时遇到了一些问题.
我找到了一些解决方案，但它们似乎不起作用.

I have a little problem with finding free rooms in reservation system.
I found some solutions, but they do not seem to work.

我的桌子:

create table room
(
   roomID               int not null,
   maxPeople            numeric(2,0) not null,
   beds                 numeric(2,0) not null,
   primary key (roomID)
);

(
   reservationID        int not null,
   clientID             int,
   roomID               int,
   startDate            date not null,
   endDate              date not null,
   primary key (reservationID)
);

我尝试过类似的操作，但这只是计算所有房间，而完全忽略了免费房间.

I tried something like this, but that's counting all rooms and totally ignore free rooms.

SELECT Id_pokoju 
from rezerwacje 
WHERE 
(
     (SELECT COUNT(ID_pokoju) 
     from rezerwacje 
     WHERE "2015-03-10" > Data_konca OR "2015-03-20" < Data_konca) 
     = 
     (SELECT COUNT(ID_pokoju) 
     from rezerwacje 
     GROUP BY Id_pokoju)
);

推荐答案

要搜索在特定时间段内没有预订的所有客房，请考虑以下事项:

To search for all rooms that have no bookings in a certain timeframe, consider the following:

SELECT roomID FROM room WHERE roomID NOT IN(
    SELECT roomID FROM reservation WHERE startDate < @EndDate AND endDate > @StartDate
)

那里发生了什么事?

在内部查询中，我正在预订列表中搜索至少部分在所需时间范围内的预订，并获取其房间ID.然后，在外部查询中，我从房间表中请求所有未列出的房间ID，也就是说，它们没有保留至少部分在所需时间范围内的预订.

In the inner query, I am searching the reservation list for reservations that are at least partially inside the required timeframe, and get their room IDs. And then, in the outer query, I request from the room table all room IDs that are NOT listed, that is, they have no reservation that is at least partially inside the required timeframe.

@StartDate和@EndDate必须由您修改-您必须将变量放在此处.

@StartDate and @EndDate would have to be modified by you - you have to put your variables there.

这篇关于MySQL在预订系统中寻找免费房间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！