在兩個不同的列中按順序查找並刪除間隙

我嘗試使用基於集合的方法來解決這個問題。但是，由於我需要查看每一行，我認為我必須使用游標；如果我錯了，請糾正我。

桌子：

Project, item, method, start, end

該表包含多個項目、多個項目，但為了簡單起見，我將其簡化為一個項目、一個項目：

數據如下所示：

ABC, widget1, XY, 1000, 1033
ABC, widget1, XY, 1033, 1062
ABC, widget1, XY, 1062, 1112
ABC, widget1, XY, 1112, 1163
ABC, widget1, WW, 1163, 1223
ABC, widget1, WW, 1223, 1288
ABC, widget1, WW, 1288, 1334
ABC, widget1, XY, 1334, 1383
ABC, widget1, XY, 1383, 1425

我想編寫一個返回此結果的查詢：

ABC, widget1, XY, 1000, 1163
ABC, widget1, WW, 1163, 1334
ABC, widget1, XY, 1334, 1425

做這個的最好方式是什麼？

您可以儲存預先計算的差距，並使用約束來確保您預先計算的數據始終是最新的：

這是表格和第一個區間

CREATE TABLE dbo.IntegerSettings(SettingID INT NOT NULL,

 IntValue INT NOT NULL,

 StartedAt DATETIME NOT NULL,

 FinishedAt DATETIME NOT NULL,

 PreviousFinishedAt DATETIME NULL,

 CONSTRAINT PK_IntegerSettings_SettingID_FinishedAt PRIMARY KEY(SettingID, FinishedAt),

 CONSTRAINT UNQ_IntegerSettings_SettingID_PreviousFinishedAt UNIQUE(SettingID, PreviousFinishedAt),

 CONSTRAINT FK_IntegerSettings_SettingID_PreviousFinishedAt

   FOREIGN KEY(SettingID, PreviousFinishedAt)

   REFERENCES dbo.IntegerSettings(SettingID, FinishedAt),

 CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt),

 CONSTRAINT CHK_IntegerSettings_StartedAt_Before_FinishedAt CHECK(StartedAt < FinishedAt)

);

GO

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

 VALUES(1, 1, '20070101', '20070103', NULL);

它有五個約束共同執行業務規則。讓我展示更複雜的工作原理。當然，有些約束很簡單，因此不需要任何解釋。

**

• 一個設置只能有一個第一個間隔

**

約束 UNQ_IntegerSettings_SettingID_PreviousFinishedAt 確保了這一點。第一個間隔沒有前一個，這意味著 PreviousFinishedAt IS NULL。UNIQUE 約束保證每個設置只能有一個這樣的行。你自己看：

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

 VALUES(1, 1, '20070104', '20070105', NULL);

/*

Server: Msg 2627, Level 14, State 2, Line 1

Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'.

The statement has been terminated.

*/

**

• 下一個視窗必須在上一個視窗結束之後開始。

**

約束 CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt 完全保證了這一點。你自己看：

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

 VALUES(1, 2, '20070104', '20070109', '20070105')

/*

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with TABLE CHECK constraint 'CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt'. The conflict occurred in database 'RiskCenter', table 'IntegerSettings'.

The statement has been terminated.

*/

**

• 兩個不同的視窗不能指代同一個視窗作為它們的前一個視窗。

**

同樣，相同的約束 UNQ_IntegerSettings_SettingID_PreviousFinishedAt 準確地保證了這一點，如下所示：

INSERT INTO dbo.IntegerSettings(SettingID, IntValue, StartedAt, FinishedAt, PreviousFinishedAt)

 VALUES(1, 3, '20070104', '20070115', '20070103')



Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint 'UNQ_IntegerSettings_SettingID_PreviousFinishedAt'. Cannot insert duplicate key in object 'dbo.IntegerSettings'.

The statement has been terminated.

這意味著不能有重疊。

如您所見，對於每個時間視窗，最多可以有一個在它之前，最多有一個在它之後。下一個間隔不能在其前一個結束之前開始。這兩個陳述一起意味著不能有重疊。

**

• 處理差距。

**

您可以完全禁止間隙，只需替換以下約束：

 CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_NotAfter_StartedAt CHECK(PreviousFinishedAt <= StartedAt),

更嚴格的，如下：

CONSTRAINT CHK_IntegerSettings_PreviousFinishedAt_EqualTo_StartedAt CHECK(PreviousFinishedAt = StartedAt),

但是，如果您允許間隙，則檢索它們的查詢非常簡單且高效，如下所示：

SELECT PreviousFinishedAt AS GapStart, StartedAt AS GapEnd
 FROM dbo.IntegerSettings
 WHERE StartedAt > PreviousFinishedAt;

遞歸 CTE 非常適合這項工作。

在這種情況下，您不需要游標，而且我敢打賭，CTE 將比任何基於游標或循環的方法來解決這個問題要好得多。

以下查詢為您提供了您所需要的確切資訊。我在 SQL Server 2008 上對其進行了測試，但如果您忽略設置塊並替換@table為目標表的名稱，您應該能夠在任何支持 CTE（如 Oracle、SQL Server 或 PostgreSQL）的平台上執行它。

-- setup
DECLARE @table TABLE (
     project   VARCHAR(10) NOT NULL
   , item      VARCHAR(20) NOT NULL
   , method    CHAR(2)     NOT NULL
   , start     INT         NOT NULL
   , [end]     INT         NOT NULL
);

INSERT INTO @table
VALUES
     ('ABC', 'widget1', 'XY', 1000, 1033)
   , ('ABC', 'widget1', 'XY', 1033, 1062)
   , ('ABC', 'widget1', 'XY', 1062, 1112)
   , ('ABC', 'widget1', 'XY', 1112, 1163)
   , ('ABC', 'widget1', 'WW', 1163, 1223)
   , ('ABC', 'widget1', 'WW', 1223, 1288)
   , ('ABC', 'widget1', 'WW', 1288, 1334)
   , ('ABC', 'widget1', 'XY', 1334, 1383)
   , ('ABC', 'widget1', 'XY', 1383, 1425)
;

-- query
WITH connected_ranges AS (
   SELECT
         right_range.project
       , right_range.method
       , right_range.item
       , right_range.start
       , right_range.[end]
   FROM
                           @table  left_range
       RIGHT OUTER JOIN    @table  right_range
           ON  right_range.project = left_range.project
           AND right_range.item    = left_range.item
           AND right_range.method  = left_range.method
           AND right_range.start   = left_range.[end]
   WHERE left_range.project IS NULL

   UNION ALL

   SELECT
         right_range.project
       , right_range.method
       , right_range.item
       , left_range.start
       , right_range.[end]
   FROM
                   connected_ranges    left_range
       INNER JOIN  @table              right_range
           ON  right_range.project = left_range.project
           AND right_range.item    = left_range.item
           AND right_range.method  = left_range.method
           AND right_range.start   = left_range.[end] 
)
--SELECT *
--FROM connected_ranges
--ORDER BY
--    project
--  , method
--  , item
--  , start
--  , [end]
--;
SELECT
     project
   , method
   , item
   , start
   , MAX([end])    AS [end]
FROM connected_ranges
GROUP BY
     project
   , method
   , item
   , start
;

總結一下我所做的：我使用遞歸 CTE 將所有連續的段連接在一起，從最左邊開始，一直到右邊。然後，在決賽中，SELECT我只拉出最大的、不重疊的部分。

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