sql - 删除其他日期中包含的日期？

Question

我有以下几行:

CREATE TABLE #TEMP (id int, name varchar(255), startdate datetime, enddate datetime)
INSERT INTO #TEMP VALUES(1, 'John', '2011-01-11 00:00:00.000','2011-01-11 00:01:10.000')
INSERT INTO #TEMP VALUES(2, 'John', '2011-01-11 00:00:20.000','2011-01-11 00:01:05.000')
INSERT INTO #TEMP VALUES(3, 'John', '2011-01-11 00:01:40.000','2011-01-11 00:01:50.000')
INSERT INTO #TEMP VALUES(4, 'Adam', '2011-01-11 00:00:40.000','2011-01-11 00:01:20.000')
INSERT INTO #TEMP VALUES(5, 'Adam', '2011-01-11 00:00:45.000','2011-01-11 00:01:15.000')

SELECT * FROM #TEMP

DROP TABLE #TEMP

我正在尝试删除日期包含在其他日期中的记录以获得以下信息:

John 2011-01-11 00:00:00.000 2011-01-11 00:01:10.000
John 2011-01-11 00:01:40.000 2011-01-11 00:01:50.000
Adam 2011-01-11 00:00:40.000 2011-01-11 00:01:20.000

关于如何为大约 100K 行的表实现此目的有什么建议吗？

Answer 1

这给出了期望的结果:

DELETE T1 FROM #TEMP T1
WHERE EXISTS(
    SELECT NULL FROM #TEMP T2
    WHERE   t1.id <> t2.id
    AND     t1.name = t2.name
    AND     t1.startdate >= t1.startdate
    AND     t1.enddate   <= t1.enddate
)

http://msdn.microsoft.com/en-us/library/ms188336.aspx

编辑:我刚刚注意到有一个问题。如果存在重复项(相同的开始日期和结束日期)，则两者都将被删除(约翰的方法不会删除任何重复项，即使只有一个相同的日期)。所以你需要考虑到这一点:

DELETE T1 FROM #TEMP T1
WHERE EXISTS(
    SELECT NULL FROM #TEMP T2
    WHERE   t1.id <> t2.id
    AND     t1.name = t2.name
    AND     t1.startdate > t2.startdate
    AND     t1.enddate   < t2.enddate
    OR      t1.id <> t2.id
    AND     t1.name = t2.name
    AND     t1.startdate = t2.startdate
    AND     t1.enddate   < t2.enddate
    OR      t1.id <> t2.id
    AND     t1.name = t2.name
    AND     t1.startdate > t2.startdate
    AND     t1.enddate   = t2.enddate
    OR      t1.id > t2.id
    AND     t1.name = t2.name
    AND     t1.startdate = t2.startdate
    AND     t1.enddate   = t2.enddate
)