可用房间的SQL查询

可用房间的SQL查询 - INN或Delphi 2010的小酒店(Sql query for available rooms - INN or small hotel for Delphi 2010)

我在Delphi 2010（TADOQuery）中使用ADO数据库。

目的地是找到可用的房间并显示小型INN的房价。

t_room

coderoom as string coderoomtype as string

t_typeroom

coderoomtype as string nameroomtype as string priceroomtype as number

t_trans

datetrans as date codepoeple as string coderoom as string dateintrans as date -> date check in dateouttrans as date -> date check out

目前我使用下面的查询来显示房价。

SELECT t_room.coderoom, t_room.coderoomtype, t_roomtype.coderoomtype, t_roomtype.nameroomtype, t_roomtype.priceroomtype FROM t_room INNER JOIN t_roomtype ON t_room.coderoomtype = t_roomtype.coderoomtype ORDER BY t_room.coderoom ASC;

并设法显示:(在Delphi 2010中的ADOQuery1和DBGrid1中）

coderoom | nameroomtype | priceroomtype ---------------------------------------- 101 | VIP | 20 102 | VIP | 20 103 | Standart | 10 104 | Standart | 10 105 | Standart | 10 106 | Standart | 10

我想要做的是如何在t_trans中显示尚未预订或尚未检查的代码室？ （具体日期）

也许像下面这样（使用NOT IN运算符）：

SELECT t_room.coderoom, t_room.coderoomtype, t_room.notesroom, t_roomtype.coderoomtype, t_roomtype.nameroomtype, t_roomtype.priceroomtype FROM t_room INNER JOIN t_roomtype ON t_room.coderoomtype = t_roomtype.coderoomtype WHERE t_room.coderoom NOT IN (SELECT * FROM t_trans WHERE [current book/checkin/out date not between dateintrans and dateoutrans] ORDER BY coderoom ASC) ORDER BY t_room.coderoom ASC;

问题是如何在t_trans.datein和t_trans.dateout之间找到未预订的可用房间？

我添加了一些文件，以便于理解我想要做的事情： http ： //sidhiciang.com/myfiles/TRIAL%20Available%20Rooms.rar

当我使用下面的代码时返回错误： $7701C41F - Exception class EOleException with message "You have writen a subquest that can return more than one field without using EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field."

代码是：

AQRoomAvailable1.SQL.Text := 'SELECT t_room.coderoom, t_room.coderoomtype, t_room.notesroom, t_roomtype.coderoomtype, t_roomtype.nameroomtype, t_roomtype.priceroomtype '; AQRoomAvailable1.SQL.Text := AQRoomAvailable1.SQL.Text + 'FROM t_room INNER JOIN t_roomtype ON t_room.coderoomtype = t_roomtype.coderoomtype WHERE t_room.coderoom '; AQRoomAvailable1.SQL.Text := AQRoomAvailable1.SQL.Text + 'NOT IN (SELECT * FROM t_trans x WHERE x.coderoom = t_room.coderoom AND ( (x.dateintrans BETWEEN ' + DateToStr(dtpDateIn1.Date) + ' AND ' + DateToStr(dtpDateOut1.Date) + ' ) '; AQRoomAvailable1.SQL.Text := AQRoomAvailable1.SQL.Text + 'OR (x.dateouttrans BETWEEN ' + DateToStr(dtpDateIn1.Date) + ' AND ' + DateToStr(dtpDateOut1.Date) + ' ) '; AQRoomAvailable1.SQL.Text := AQRoomAvailable1.SQL.Text + 'OR (' + DateToStr(dtpDateIn1.Date) + ' BETWEEN x.dateintrans AND x.dateouttrans) ) )';

我已经阅读下面的链接，但没有找到答案，更加困惑....

使用SQL检查房间的可用性 mysql酒店房间供应情况 可预订的房间[酒店预订] 查询酒店预订的可用房间 选择可用的房间 在房间可用子查询上选择房间类型 房间预订查询 房间预订sql查询 SQL计划 - 选择所有可用于给定日期范围的房间 SQL内部连接3个表？ 我如何使用ID连接多个SQL表？ SQL查询不在两个日期之间

I use ADO database in Delphi 2010 (TADOQuery).

The destination is to find the available rooms and show its room rate for a small INN.

t_room

coderoom as string coderoomtype as string

t_typeroom

coderoomtype as string nameroomtype as string priceroomtype as number

t_trans

datetrans as date codepoeple as string coderoom as string dateintrans as date -> date check in dateouttrans as date -> date check out

Currently I use the query below to show the room prices.

SELECT t_room.coderoom, t_room.coderoomtype, t_roomtype.coderoomtype, t_roomtype.nameroomtype, t_roomtype.priceroomtype FROM t_room INNER JOIN t_roomtype ON t_room.coderoomtype = t_roomtype.coderoomtype ORDER BY t_room.coderoom ASC;

And manage to show : (in ADOQuery1 and DBGrid1 in Delphi 2010)

coderoom | nameroomtype | priceroomtype ---------------------------------------- 101 | VIP | 20 102 | VIP | 20 103 | Standart | 10 104 | Standart | 10 105 | Standart | 10 106 | Standart | 10

What I want to do is how to show the coderoom s that has not been booked or has not been checkedin within t_trans ? (for specific dates)

Maybe something like below (using NOT IN operator) :

SELECT t_room.coderoom, t_room.coderoomtype, t_room.notesroom, t_roomtype.coderoomtype, t_roomtype.nameroomtype, t_roomtype.priceroomtype FROM t_room INNER JOIN t_roomtype ON t_room.coderoomtype = t_roomtype.coderoomtype WHERE t_room.coderoom NOT IN (SELECT * FROM t_trans WHERE [current book/checkin/out date not between dateintrans and dateoutrans] ORDER BY coderoom ASC) ORDER BY t_room.coderoom ASC;

The question is how to find available rooms that aren't booked between the t_trans.datein and t_trans.dateout ?

I add some files to make it easy to understand what I want to do at : http://sidhiciang.com/myfiles/TRIAL%20Available%20Rooms.rar

When I use the code below return error : $7701C41F - Exception class EOleException with message "You have writen a subquest that can return more than one field without using EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field."

The code are :

AQRoomAvailable1.SQL.Text := 'SELECT t_room.coderoom, t_room.coderoomtype, t_room.notesroom, t_roomtype.coderoomtype, t_roomtype.nameroomtype, t_roomtype.priceroomtype '; AQRoomAvailable1.SQL.Text := AQRoomAvailable1.SQL.Text + 'FROM t_room INNER JOIN t_roomtype ON t_room.coderoomtype = t_roomtype.coderoomtype WHERE t_room.coderoom '; AQRoomAvailable1.SQL.Text := AQRoomAvailable1.SQL.Text + 'NOT IN (SELECT * FROM t_trans x WHERE x.coderoom = t_room.coderoom AND ( (x.dateintrans BETWEEN ' + DateToStr(dtpDateIn1.Date) + ' AND ' + DateToStr(dtpDateOut1.Date) + ' ) '; AQRoomAvailable1.SQL.Text := AQRoomAvailable1.SQL.Text + 'OR (x.dateouttrans BETWEEN ' + DateToStr(dtpDateIn1.Date) + ' AND ' + DateToStr(dtpDateOut1.Date) + ' ) '; AQRoomAvailable1.SQL.Text := AQRoomAvailable1.SQL.Text + 'OR (' + DateToStr(dtpDateIn1.Date) + ' BETWEEN x.dateintrans AND x.dateouttrans) ) )';

I already read links below and did not find the answer and gotten more confused....

check availability of a room with SQL mysql hotel room availability listing rooms available[hotel reservation] query for available rooms in hotel reservation Select available rooms selecting room type on room availabilty subquery Room Booking Query Room booking sql query SQL Scheduling - Select All Rooms Available for Given Date Range SQL Inner-join with 3 tables? How can I join multiple SQL tables using the IDs? SQL Query NOT Between Two Dates

最满意答案

SQL似乎不是这个问题的主要问题。 要查找所有研磨范围，您必须区分4种情况，其中案例2是案例1或3的特例。

SW和EW之间的EE 已经被案例1和案件3抓住了 SW和EW之间的SE SE和EE之间的SW

如果您想使用参数进行查询，那么您应该根据数据库引擎使用参数，如果您能够在SQL中声明变量以避免使用超出需要的参数。 一个示例SQL可能看起来像（根据您的结束和开始日期的方式，您可能需要添加/减去参数的参数）：

Declare @SW datetime Declare @EW datetime Select @SW=:SW Select @EW=:EW SELECT t_room.coderoom, t_room.coderoomtype, t_roomtype.coderoomtype, t_roomtype.nameroomtype, t_roomtype.priceroomtype FROM t_room INNER JOIN t_roomtype ON t_room.coderoomtype = t_roomtype.coderoomtype WHERE t_room.coderoom NOT IN (SELECT x.coderoom FROM t_trans x WHERE (x.dateouttrans between @SW and @EW ) OR (x.dateintrans between @SW and @EW ) OR (@SW between x.dateintrans and x.dateouttrans) ) ORDER BY t_room.coderoom ASC;

编辑从评论中回答

由于Access无法使用本地变量，因此您必须使用5个参数，不应尝试创建不带参数的SQL。 AQRoomAvailable1的SQL看起来像：

SELECT t_room.coderoom, t_room.coderoomtype, t_roomtype.coderoomtype, t_roomtype.nameroomtype, t_roomtype.priceroomtype FROM t_room INNER JOIN t_roomtype ON t_room.coderoomtype = t_roomtype.coderoomtype WHERE t_room.coderoom NOT IN (SELECT x.coderoom FROM t_trans x where (x.dateouttrans between :SW and :EW ) OR (x.dateintrans between :SW1 and :EW1 ) OR (:SW2 between x.dateintrans and x.dateouttrans) ) ORDER BY t_room.coderoom ASC;

将参数的数据类型更改为ftDateTime：

将您的Action actRoomCheckIn1更改为：

procedure TFMain.actRoomCheckIn1Execute(Sender: TObject); begin if (dtpDateOut1.Date >= dtpDateIn1.Date) then begin AQRoomAvailable1.Close; AQRoomAvailable1.Parameters.ParamByName('SW').Value := dtpDateIn1.Date; AQRoomAvailable1.Parameters.ParamByName('EW').Value := dtpDateOut1.Date; AQRoomAvailable1.Parameters.ParamByName('SW1').Value := dtpDateIn1.Date; AQRoomAvailable1.Parameters.ParamByName('EW1').Value := dtpDateOut1.Date; AQRoomAvailable1.Parameters.ParamByName('SW2').Value := dtpDateIn1.Date; AQRoomAvailable1.Open; end else begin AQRoomAvailable1.Active := False; end; end;

The SQL does not seem to the main problem in this question. To find all lapping ranges you will have to differentiate 4 cases, where case 2 is a special case of case 1 or 3.

EE between SW and EW already caught by case 1 and 3 SE between SW and EW SW between SE and EE

If you want to use parameters for you query, which you should, it is depending from you database engine if you are able to declare variables in your SQL to avoid the need of using more parameters than needed. An example SQL could look like (depending of the way you are stroring end and start days you might need to add/subtract an offest to your parameters):

Declare @SW datetime Declare @EW datetime Select @SW=:SW Select @EW=:EW SELECT t_room.coderoom, t_room.coderoomtype, t_roomtype.coderoomtype, t_roomtype.nameroomtype, t_roomtype.priceroomtype FROM t_room INNER JOIN t_roomtype ON t_room.coderoomtype = t_roomtype.coderoomtype WHERE t_room.coderoom NOT IN (SELECT x.coderoom FROM t_trans x WHERE (x.dateouttrans between @SW and @EW ) OR (x.dateintrans between @SW and @EW ) OR (@SW between x.dateintrans and x.dateouttrans) ) ORDER BY t_room.coderoom ASC;

EDIT to answer from the comment

Since Access is not capable to use local variables you will have to use 5 parameters, you should not try to create the SQL without parameters. The SQL of AQRoomAvailable1 would look like:

SELECT t_room.coderoom, t_room.coderoomtype, t_roomtype.coderoomtype, t_roomtype.nameroomtype, t_roomtype.priceroomtype FROM t_room INNER JOIN t_roomtype ON t_room.coderoomtype = t_roomtype.coderoomtype WHERE t_room.coderoom NOT IN (SELECT x.coderoom FROM t_trans x where (x.dateouttrans between :SW and :EW ) OR (x.dateintrans between :SW1 and :EW1 ) OR (:SW2 between x.dateintrans and x.dateouttrans) ) ORDER BY t_room.coderoom ASC;

Change the datatype of the parameters to ftDateTime:

Change your Action actRoomCheckIn1 to:

procedure TFMain.actRoomCheckIn1Execute(Sender: TObject); begin if (dtpDateOut1.Date >= dtpDateIn1.Date) then begin AQRoomAvailable1.Close; AQRoomAvailable1.Parameters.ParamByName('SW').Value := dtpDateIn1.Date; AQRoomAvailable1.Parameters.ParamByName('EW').Value := dtpDateOut1.Date; AQRoomAvailable1.Parameters.ParamByName('SW1').Value := dtpDateIn1.Date; AQRoomAvailable1.Parameters.ParamByName('EW1').Value := dtpDateOut1.Date; AQRoomAvailable1.Parameters.ParamByName('SW2').Value := dtpDateIn1.Date; AQRoomAvailable1.Open; end else begin AQRoomAvailable1.Active := False; end; end;