SQL - 选择下一个日期查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3609671/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL - Select next date query
提问by John
I have a table with many IDs and many dates associated with each ID, and even a few IDs with no date. For each ID and date combination, I want to select the ID, date, and the next largest date also associated with that same ID, or null as next date if none exists.
我有一个表，其中包含许多 ID 和与每个 ID 关联的许多日期，甚至还有一些没有日期的 ID。对于每个 ID 和日期组合，我想选择 ID、日期和与同一 ID 关联的下一个最大日期，或者如果不存在则将下一个日期设为 null。
Sample Table:
示例表：
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
Desired Output:
期望输出：
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
回答by Daniel Renshaw
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
This has been tested on SQL Server 2008 R2 (but it should work on other DBMSs) and produces the following output:
这已经在 SQL Server 2008 R2 上进行了测试（但它应该适用于其他 DBMS）并产生以下输出：
id date NextDate ----------- ----------------------- ----------------------- 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 NULL 3 2010-08-15 00:00:00.000 NULL 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
Update 1:For those that are interested, I've compared the performance of the two variants in SQL Server 2008 R2 (one uses MIN aggregate and the other uses TOP 1 with an ORDER BY):
更新 1：对于那些感兴趣的人，我比较了 SQL Server 2008 R2 中两个变体的性能（一个使用 MIN 聚合，另一个使用带有 ORDER BY 的 TOP 1）：
Without an index on the date column, the MIN version had a cost of 0.0187916 and the TOP/ORDER BY version had a cost of 0.115073 so the MIN version was "better".
如果日期列没有索引，MIN 版本的成本为 0.0187916，TOP/ORDER BY 版本的成本为 0.115073，因此 MIN 版本“更好”。
With an index on the date column, they performed identically.
使用日期列上的索引，它们的性能相同。
Note that this was testing with just these 9 records so the results could be (very) spurious...
请注意，这是仅使用这 9 条记录进行测试，因此结果可能（非常）虚假...
Update 2:The results hold for 10,000 uniformly distributed random records. The TOP/ORDER BY query takes so long to run at 100,000 records I had to cancel it and give up.
更新 2：结果适用于 10,000 条均匀分布的随机记录。TOP/ORDER BY 查询需要很长时间才能运行 100,000 条记录，我不得不取消它并放弃。
回答by Андрей Костенко
SELECT
id,
date,
( SELECT date
FROM table t1
WHERE t1.date > t2.date
ORDER BY t1.date LIMIT 1 )
FROM table t2
SELECT
id,
date,
( SELECT date
FROM table t1
WHERE t1.date > t2.date
ORDER BY t1.date LIMIT 1 )
FROM table t2
回答by Bharat
If your db is oracle, you can use lead() and lag()functions.
如果您的数据库是 oracle，则可以使用lead() and lag()函数。
SELECT id, date,
LEAD(date, 1, 0) OVER (PARTITION BY ID ORDER BY Date DESC NULLS LAST) NEXT_DATE,
FROM Your_table
ORDER BY ID;
