SQL - 查找时间表中的空位

Question

我正在使用 SQLite 数据库，我有三个表描述建筑物、房间和预定的事件。

这些表如下所示：

• Buildings(ID,Name)
• Rooms(ID,BuildingID,Number)
• Events(ID,BuildingID,RoomID,Days,s_time,e_time)

因此每个事件都与建筑物和房间相关联。 Days 列包含一个整数，它是与一周中的天对应的质数的乘积（值 21 表示事件发生在星期二 = 3 和星期四 = 7）。

我希望找到一种方法来生成特定建筑物中将在接下来的几个小时内开放的房间的报告，以及它们将开放多长时间。

这是我到目前为止所拥有的：

SELECT Rooms.Number 
FROM Rooms
INNER JOIN Buildings on ( Rooms.BuildingID = Buildings.ID )
WHERE 
  Buildings.Name = "BuildingName"

EXCEPT

SELECT Events.RoomID
FROM Events
INNER JOIN Buildings on ( Events.BuildingID = Buildings.ID )
WHERE
  Buildings.Name = "BuildingName" AND
  Events.days & 11 = 0 AND
  time("now", "localtime" BETWEEN events.s_time AND events.e_time;

在这里，我找到特定建筑物的所有房间，然后删除当前正在进行预定活动的房间。

我期待所有有用的提示/评论。

Answer 1

如果您将日期存储为素数的乘积，则模 (%) 运算符可能更有用：

SELECT * FROM Events
INNER JOIN Buildings on (Events.BuildingID = Buildings.ID)
WHERE
    (Events.Days % 2 = 0 AND Events.Days % 5 = 0)

选择星期一或星期三发生的事件。

但我确实必须指出，存储素数的乘积在计算和存储上都非常昂贵。存储两个幂的总和（周一 = 1、周二 = 2、周三 = 4、周四 = 8、周五 = 16、周六 = 32、周日 = 64）要容易得多。

当前实施的最大可能值为 510,510。存储此类数字的最小数据类型是 int（每行 32 位），检索编码数据需要最多 7 次模 (%) 运算。

2^n 求和方法的最大可能值是 127，它可以存储在tinyint（每行 8 位）中，并且检索编码数据将使用按位与 (&)，这更便宜（因此更快）。

对于您正在使用的内容来说可能不是问题，但是选择任何可以为您提供最佳空间和性能效率的方法是一个好习惯，以免您在更大规模地实施解决方案时遇到严重问题。