SQL 确定多个日期范围 (SQL Server 2000)

我有一个表，其中包含一个ID and a Date为一个事件。每一行代表一个日期。我正在尝试确定连续的日期范围并合并输出以显示ID,StartDate,EndDate

ID      Date
200236  2011-01-02 00:00:00.000
200236  2011-01-03 00:00:00.000
200236  2011-01-05 00:00:00.000
200236  2011-01-06 00:00:00.000
200236  2011-01-07 00:00:00.000
200236  2011-01-08 00:00:00.000
200236  2011-01-09 00:00:00.000
200236  2011-01-10 00:00:00.000
200236  2011-01-11 00:00:00.000
200236  2011-01-12 00:00:00.000
200236  2011-01-13 00:00:00.000
200236  2011-01-15 00:00:00.000
200236  2011-01-16 00:00:00.000
200236  2011-01-17 00:00:00.000

输出如下所示：

ID       StartDate    EndDate
200236   2011-01-02   2011-01-03
200236   2011-01-05   2011-01-13
200236   2011-01-15   2011-01-17

关于如何在 SQL Server 2000 中处理这个问题有什么想法吗？

SELECT ...
FROM   ...
WHERE  date_column BETWEEN '2011-01-02' AND '2011-01-15'

perhaps? ^Reference

或者，您可以执行子查询并使用 MAX 链接下一条记录，其中日期

SELECT id, date, (SELECT MAX(date) FROM mytable WHERE date <= mytable.date) AS nextDate
FROM   mytable

Or use:

SELECT TOP 1 date
FROM         mytable
WHERE        date <= mytable.date AND id <> mytable.id
ORDER BY     date

作为子查询，它会获取当前记录之后的下一个日期。