SQL - 选择下一个日期查询

Question

我有一个表，其中包含许多 ID 和与每个 ID 关联的许多日期，甚至还有一些没有日期的 ID。对于每个 ID 和日期组合，我想选择 ID、日期和与同一 ID 关联的下一个最大日期，或者如果不存在则将下一个日期设为 null。

示例表:

ID      Date
1       5/1/10
1       6/1/10
1       7/1/10
2       6/15/10
3       8/15/10
3       8/15/10
4       4/1/10
4       4/15/10
4       

期望输出:

ID       Date       Next_Date
1        5/1/10     6/1/10
1        6/1/10     7/1/10
1        7/1/10     
2        6/15/10    
3        8/15/10    
3        8/15/10    
4        4/1/10     4/15/10
4        4/15/10    

Answer 1

SELECT
    mytable.id,
    mytable.date,
    (
        SELECT
            MIN(mytablemin.date)
        FROM mytable AS mytablemin
        WHERE mytablemin.date > mytable.date
            AND mytable.id = mytablemin.id
    ) AS NextDate
FROM mytable

这已经在 SQL Server 2008 R2 上进行了测试(但它应该适用于其他 DBMS)并产生以下输出:

身份证日期下一个日期
—————————————————————————————— -------
1 2010-05-01 00:00:00.000 2010-06-01 00:00:00.000
1 2010-06-01 00:00:00.000 2010-06-15 00:00:00.000
1 2010-07-01 00:00:00.000 2010-08-15 00:00:00.000
2 2010-06-15 00:00:00.000 2010-07-01 00:00:00.000
3 2010-08-15 00:00:00.000 空
3 2010-08-15 00:00:00.000 空
4 2010-04-01 00:00:00.000 2010-04-15 00:00:00.000
4 2010-04-15 00:00:00.000 2010-05-01 00:00:00.000
4 NULL NULL

更新 1:
对于那些感兴趣的人，我比较了 SQL Server 2008 R2 中两个变体的性能(一个使用 MIN 聚合，另一个使用带有 ORDER BY 的 TOP 1):

如果日期列没有索引，MIN 版本的成本为 0.0187916，TOP/ORDER BY 版本的成本为 0.115073，因此 MIN 版本“更好”。

使用日期列上的索引，它们的表现相同。

请注意，这是仅使用这 9 条记录进行测试，因此结果可能(非常)虚假...

更新 2:
结果适用于 10,000 条均匀分布的随机记录。 TOP/ORDER BY 查询需要很长时间才能运行 100,000 条记录，我不得不取消它并放弃。