計算區間表的集差

我經常遇到以下問題。我有兩張間隔表。它們以日期為界（沒有時間成分）。在每個表中，間隔不重疊。

我想找到這兩個表的集合差異，即表示第一個表中的時間而不是第二個表的時間間隔。

上述虛擬範例的所需輸出：

即，如果第一個表的日期在下面標記為黃色，並且第二個表的範圍用一個框包圍，我會尋找未裝箱的黃色日期的連續範圍。

我目前將時間間隔視為兩端包含，並使用 DateTime 作為我的時間戳。我目前的方法是通過三重自連接 (yuck) 對第二個表進行補充，然後通過連接將結果與第一個表相交。不好玩。

有更好的方法嗎？

考慮到各種簡化假設（僅日期且表格中沒有重疊間隔），我可能會考慮讓事情保持簡單。

首先創建一個輔助數字表（從零開始）

CREATE TABLE dbo.SmallNumbers
(
Number SMALLINT PRIMARY KEY
)

INSERT INTO dbo.SmallNumbers
SELECT TOP 32768 ROW_NUMBER() OVER (ORDER BY @@SPID)-1 AS Number
FROM sys.all_columns c1, sys.all_columns c2

然後下面的程式碼將範圍擴展到它們的組成日期，用於EXCEPT查找差異以及間隙和孤島技術以將範圍折疊回去。因為每個日期只會出現一次，我們可能只談論每十年擴展到幾千個日期。

WITH UnmatchedDates(Date) AS
(
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table1
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
EXCEPT 
SELECT DATEADD(DAY,N.Number, StartTs)
FROM Table2
JOIN dbo.SmallNumbers N ON N.Number <= DATEDIFF(DAY, StartTs, EndTs)
),
UnmatchedDatesWithGrp(Date, Grp) AS
(
SELECT Date, 
      DATEDIFF(DAY, 0, Date) - ROW_NUMBER() OVER (ORDER BY Date)
FROM UnmatchedDates
)
SELECT StartTs = MIN(Date), 
        EndTs = MAX(Date)
FROM UnmatchedDatesWithGrp
GROUP BY Grp

引用自：https://dba.stackexchange.com/questions/305832