android - SQLite 子查询不工作

Question

表名称:MRM_BOOKING_DETAILS
示例数据:

Booking_id USER_ID ROOM_ID CHECKIN             CHECKOUT
1          hary    1       2012-04-16 09:00:00 2012-04-16 09:30:00
2          jenny   2       2012-04-17 13:00:00 2012-04-17 13:30:00
3          steve   1       2012-04-16 15:00:00 2012-04-16 15:30:00

等等……

Booking_id 是主键。 Room_Id 是外键 w.r.t. Room_Details_Table 以 Room_Id 作为 PK。

我的目标是获取用户在特定日期、特定时间输入的可用记录列表。为此，我想出了这个查询:

SELECT ROOMNO,BUILDINGNO 
FROM MRM_ROOM_DETAILS 
WHERE ROOMID IN (SELECT distinct roomid 
                FROM MRM_BOOKING_DETAILS 
                WHERE (CHECKIN NOT BETWEEN '2012-04-13 09:50:00' AND '2012-04-13 10:20:00')
                  and (CHECKOUT NOT BETWEEN '2012-04-13 09:50:00' AND '2012-04-13 10:20:00'))
  AND CAPACITY > 15 AND PROJECTIONSTATUS = 'NO';

Capacity 和 ProjectionStatus 是 Room details 表中的列，请暂时忽略该部分。

CHECKIN 和 CHECKOUT 是用户在搜索屏幕中提供的值。

但不幸的是子查询不正确，因此我没有得到想要的结果。

SELECT distinct roomid 
FROM MRM_BOOKING_DETAILS 
WHERE (CHECKIN NOT BETWEEN '2012-04-13 09:50:00' AND '2012-04-13 10:20:00') and
      (CHECKOUT NOT BETWEEN '2012-04-13 09:50:00' AND '2012-04-13 10:20:00');

这就是为什么我说子查询不正确。假设用户提供的值是:

CHECKIN : 2012-04-16 09:00:00 and CHECKOUT: 2012-04-16 09:30:00

根据表中的上述数据，将返回 ROOM_ID 1 和 2。对于第一行，它将失败，但对于第二行和第三行它将成功，因此它将返回 1 和 2，这是不正确的。正确的结果应该只有2。

请帮助我获得正确的查询。

Answer 1

将您的逻辑从“在 x 以外的时间预订的房间”更改为“在 x 时间未预订的房间”。

此外，您可能应该使用 DATETIME 函数。

更新

我已经添加了对所有路口的检查，并且没有使用包容性 BETWEEN，因此可以在 8:30 到 9:00 和 9:00 到 9:30 之间预订房间.

SELECT ROOMNO, BUILDINGNO 
FROM MRM_ROOM_DETAILS 
WHERE ROOMID NOT IN (
    SELECT DISTINCT ROOM_ID as ROOMID
    FROM MRM_BOOKING_DETAILS 
    WHERE (DATETIME(CHECKIN) > DATETIME('2012-04-16 08:30:00') AND DATETIME(CHECKIN) < DATETIME('2012-04-16 09:31:00'))
        OR (DATETIME(CHECKOUT) > DATETIME('2012-04-16 08:30:00') AND DATETIME(CHECKOUT) < DATETIME('2012-04-16 09:31:00'))
        OR (DATETIME('2012-04-16 08:30:00') > DATETIME(CHECKIN) AND DATETIME('2012-04-16 08:30:00') < DATETIME(CHECKOUT))
        OR (DATETIME('2012-04-16 09:31:00') > DATETIME(CHECKIN) AND DATETIME('2012-04-16 09:31:00') < DATETIME(CHECKOUT))
)
    AND CAPACITY > 15
    AND PROJECTIONSTATUS = 'NO';