tsql - 如何使用 SQL 检查表中的日期不重叠

Question

我有一个包含开始和结束日期时间的表格，我需要确定是否有重叠，并且不太确定最好的方法。

最初我想使用如下所示的嵌套游标，它确实有效，但是我正在相互检查相同的记录两次，而且我确定它不是很有效。

例如:此表会导致重叠。

id  start                       end
-------------------------------------------------------
1   2009-10-22 10:19:00.000     2009-10-22 11:40:00.000
2   2009-10-22 10:31:00.000     2009-10-22 13:34:00.000
3   2009-10-22 16:31:00.000     2009-10-22 17:34:00.000

Declare @Start datetime, @End datetime, @OtherStart datetime, @OtherEnd datetime, @id int, @endCheck bit

Set @endCheck = 0

DECLARE Cur1 CURSOR FOR
        select id, start, end from table1 

OPEN Cur1
FETCH NEXT FROM Cur1 INTO @id, @Start, @End
WHILE @@FETCH_STATUS = 0 AND @endCheck = 0
BEGIN
    -- Get a cursor on all the other records
    DECLARE Cur2 CURSOR FOR
            select start, end from table1 
                and id != @id AND start < @end
    OPEN Cur2
    FETCH NEXT FROM Cur2 INTO @OtherStart, @OtherEnd
    WHILE @@FETCH_STATUS = 0 AND @endCheck = 0
    BEGIN

            if ( @Start > @OtherStart AND @Start < @OtherEnd    OR
                 @End > @OtherStart AND @End < @OtherEnd )
                or
               ( @OtherStart > @Start AND @OtherStart < @End    OR
                 @OtherEnd > @Start AND @OtherEnd < @End )

            BEGIN
                SET @endCheck = 1
            END

            FETCH NEXT FROM Cur2 INTO @OtherStart, @OtherEnd
    END
    CLOSE Cur2
    DEALLOCATE Cur2

    FETCH NEXT FROM Cur1 INTO @id, @Start, @End
END
CLOSE Cur1
DEALLOCATE Cur1

Answer 1

首先，让我们彻底检查“重叠”的含义并优化您的 bool 表达式。

重叠是以下任何一种情况:

                 Start1                     End1--------------------------------------------------------------------------------- Start2                       End2 Start2                                                       End2                             Start2                           End2

And is NOT any of the following conditions:

                 Start1                     End1--------------------------------------------------------------------------------- Start2  End2                                                      Start2  End2

If you look carefully, you'll notice Start2 < End1 AND End2 > Start1 defines this relationship, so you can reduce your expression to this.

---

• Second, you can put this in a WHERE condition for your cursor instead of looping through every row and checking.

• Third, since you're just checking whether something exists or not, you can use an IF EXISTS clause instead of looping through a cursor and checking.

• Finally, you can condense this all down to a single query using an INNER JOIN on itself or WHERE EXISTS, depending on how you want your final output to look. To get all the overlaps in the table, you can try something like the following (the t2.id > t1.id is to only get each overlap once):

  SELECT t1.id, t2.id
  FROM MyTable t1
  INNER JOIN MyTable t2 ON t2.id > t1.id 
                        AND t2.start < t1.end AND t2.end > t1.start