SQL检查，如果一系列timebands

Question

的我想写SQL检查表A中给定的时间（如上午05时24）是表下面的不规则时间段之一内B.

5:00 AM to 6:00 AM 

7:30 AM to 9:15 AM 

1:26 PM to 2:45 PM 

4:00 PM to 4:59 PM 

10:00 PM to 11:14 PM 

时间内

我的计划是写这条SQL语句：

CASE 
    WHEN [Given Time] BETWEEN [START TIME] and [END TIME] THEN 1 
    ELSE 0 
END 

但后来我意识到我需要写5加入 - 有另一种方式？

山姆

Answer 1

请试试这个：

CASE 
    WHEN EXISTS (SELECT 1 FROM [My Time Table] 
       WHERE [Given Time] BETWEEN [START TIME] and [END TIME]) 
    THEN 1 
    ELSE 0 
END 

Answer 2

我没有看到一个CASE语句或JOIN一个原因。这应该就够了。

DECLARE @YourTime time = '05:42' 

SELECT 
    * 
FROM 
    YourTable 
WHERE 
    @YourTime between [Start Time] and [End Time] 

或者更明确地......

... 
WHERE 
    [Start Time] <= @YourTime 
    AND [End Time] >= @YourTime