sql - 如何找到表中不存在的值？

Question

我有一个名为 Bookings 的简化表，其中包含两列 BookDate 和 BookSlot。 BookDate 列将仅包含日期(没有时间)，BookSlot 列将包含一天中的时间以 30 分钟为间隔，从 0 开始至 1410(含)。 (即 600 = 上午 10:00)

如何不通过循环找到将来可用的第一个空位(未预订)？

以下是表定义和测试数据:

Create Table Bookings(
    BookDate DateTime Not Null,
    BookSlot Int Not Null
)
Go

Insert Into Bookings(BookDate,BookSlot) Values('2014-07-01',0);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-01',30);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-01',60);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-01',630);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-02',60);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-02',90);
Insert Into Bookings(BookDate,BookSlot) Values('2014-07-02',120);

我想要一种方法来返回不在表中且在未来的第一个可用插槽(基于服务器时间)。

基于上述测试数据:

• 如果当前服务器时间为7 月 1 日上午 00:10，则结果应为7 月 1 日上午 90 分钟(上午 01:30)。
• 如果当前服务器时间为7 月 2 日凌晨 01:05，则结果应为7 月 2 日凌晨 150 分钟(凌晨 02:30)。

如果 future 没有预订，该函数将简单地返回 future 最近的半小时。

--

用于此的 SQL Fiddle 在这里:

http://sqlfiddle.com/#!6/0e93d/1

Answer 1

以下是一种允许在未来 256 天进行预订的方法，并且允许空预订​​表。我假设您使用的是 SQL Server 2005，因为您的 BookDate 是 dateTime 而不是 date。
在任何情况下，您都可以考虑将槽存储为完整的日期时间而不是单独的列。这将方便查询并提高性能。

DECLARE @now DATETIME = '2014-07-01 00:10:00'; 

WITH T4 
     AS (SELECT N 
         FROM   (VALUES(0), 
                       (0), 
                       (0), 
                       (0), 
                       (0), 
                       (0), 
                       (0), 
                       (0)) AS t(N)), 
     T256 
     AS (SELECT Row_number() 
                  OVER( 
                    ORDER BY (SELECT 0)) - 1 AS n 
         FROM   T4 AS a 
                CROSS JOIN T4 AS b 
                CROSS JOIN T4 AS c), 
     START_DATE 
     AS (SELECT Dateadd(DAY, Datediff(DAY, '', @now), '') AS start_date), 
     START_TIME 
     AS (SELECT Dateadd(MINUTE, Datediff(MINUTE, '', @now) / 30 * 30, '') AS 
                start_time), 
     DAILY_INTERVALS 
     AS (SELECT N * 30 AS interval 
         FROM   T256 
         WHERE  N < 48) 
SELECT TOP (1) Dateadd(DAY, future_days.N, START_DATE) AS BookDate, 
               DAILY_INTERVALS.INTERVAL                AS BookSlot 
FROM   START_DATE 
       CROSS APPLY START_TIME 
       CROSS APPLY DAILY_INTERVALS 
       CROSS APPLY T256 AS future_days 
WHERE  Dateadd(MINUTE, DAILY_INTERVALS.INTERVAL, 
              Dateadd(DAY, future_days.N, START_DATE)) > START_TIME 
       AND NOT EXISTS(SELECT * 
                      FROM   DBO.BOOKINGS 
                      WHERE  BOOKDATE = START_DATE 
                             AND BOOKSLOT = DAILY_INTERVALS.INTERVAL) 
ORDER  BY BOOKDATE, 
          BOOKSLOT; 

查看此SQL Fiddle