Sql 查询可用房间 - Delphi 2010 的 INN 或小型酒店

Question

我在 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 中显示尚未预订或尚未 checkin 的 coderoom ？ (具体日期)

可能类似于下面的内容(使用 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) ) )';

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

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

Answer 1

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

SW 和 EW 之间的 EE

已被案例 1 和 3

捕获

SW 和 EW 之间的 SE

SE和EE之间的SW

如果您想为查询使用参数，您应该这样做，这取决于您的数据库引擎是否能够在 SQL 中声明变量以避免使用比需要更多的参数。一个示例 SQL 可能看起来像(取决于您存储结束和开始日期的方式，您可能需要在参数中添加/减去一个offest):

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:


将您的操作 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;