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

Question

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

目的是找到可用的房间，并显示一家小旅馆的房间价格。

房间（_R）

  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;

并设法显示：（在Delphi2010的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内尚未预订或未签入的代码室？（具体日期）

也许像这样（使用非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可用房间.rar

当我使用下面的代码时，返回错误：$7701C41F-异常类EOleException，带有消息“您编写了一个子查询，它可以返回多个字段，而无需在主查询的From子句中使用EXISTS保留字。修改子查询的SELECT语句，只请求一个字段。”

代码是：

  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查询不是在两个日期之间

Answer 1

两点意见：

< li >通常使用整数作为主键(和外键),而不是字符串，正如您在这里所做的那样 < li >我不清楚t_trans.datetrans、t_trans.dateintrans和t_trans.dateouttrans之间的区别。

如果dateintrans是预订的开始，dateouttrans是结束，那么您需要的查询可能如下

SELECT t_room.coderoom
from t_room
where not exists (select 1 from t_trans
where t_trans.coderoom = t_room.coderoom
and t_trans.dateintrrans >= :p1
and t_trans.dateouttrans <= :p1)

:p1是您要检查的日期。

Answer 2

1. EE在SW和EW之间
2. 已经被案例1和3抓住了
3. SE在SW和EW之间
4. 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;

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