SQL - 选择下一个日期查询
我有一个包含许多ID和许多与每个ID关联的日期的表,甚至有几个没有日期的ID。对于每个ID和日期组合,我想选择ID,日期和与该ID相关的下一个最大日期,如果不存在,则选择Null作为下一个日期。SQL - 选择下一个日期查询
样品表:
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
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工作),并产生以下的输出:
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
更新1: 对于那些有兴趣,我比较了SQL Server 2008 R2中两个变体的性能(一个使用MIN聚合,另一个使用TOP 1和ORDER BY):
如果没有日期列索引,MIN版本的成本为0.0187916,TOP/ORDER BY版本的成本为0.115073,所以MIN版本“更好”。
使用日期列上的索引,它们的表现相同。
注意,这些只在这9条记录测试所以结果可能是(非常)杂散...
更新2: 结果持有10,000均匀分布的随机记录。 TOP/ORDER BY查询需要很长时间才能运行100,000条记录,我不得不取消它并放弃。
最好使用排序而不是汇总函数。它有一张大桌子,尤其是 – 2010-08-31 14:04:46
@Andrii:我不能说其他数据库,但在SQL Server上它不应该有所作为。如果有索引,它足够聪明,知道它可以读取第一行;如果没有索引,它必须以任何方式扫描整个表格。实际上,使用ORDER BY可能会比较慢,因为它必须执行O(n * lg(n))排序而不是O(n)扫描。 – 2010-08-31 14:08:23
这是一个mssql数据库,查询通过访问传递,这意味着LIMIT查询无法正常工作。上面的min查询完美地与在WHERE语句中添加mytable.id = mytablemin.id的轻微添加完美结合。该查询有点呆滞,但目前我正在使用的日期字段上没有索引。 谢谢大家的帮助。 – John 2010-08-31 15:00:53
SELECT id, date, (SELECT date FROM table t1 WHERE t1.date > t2.date ORDER BY t1.date LIMIT 1) FROM table t2
如果你的数据库是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;
上面的代码为我产生一个错误 - 0应该是空的,因为oracle sql抱怨它是一个不兼容的数据类型(数字而不是日期)下面的工作正常,但:SELECT id,date,LEAD (日期,1,空)OVER(从日期中删除ID的日期)DESC NULLS LAST)NEXT_DATE,FROM Your_table – bawpie 2012-04-18 12:15:13
什么是你的数据库 – Bharat 2010-08-31 14:03:40