请问如何求现有时间差？

[HarleyTung]

ID      CreateTime
1       2009-01-08 12:00:00
2       2009-01-01 12:00:00
3       2009-01-07 12:00:00
4       2009-01-06 12:00:00
5       2009-01-03 12:00:00
6       2009-01-09 12:00:00
7       2009-01-15 12:00:00

数据如上

我想求出现有时间的差。
比如最晚一天时间与ID=3的时间天数差，最晚是2009-01-15 而ID=3是 2009-01-07
时间差就应该是3天

这个不能用datediff 因为不是求两个时间差，而是现有时间的差，请问SQL语句要怎么写？谢谢！

[水族杰纶]

结果什么样的?

[水族杰纶]

[Quote=引用楼主 dyjqk 的帖子:]
SQL code
ID CreateTime
1 2009-01-08 12:00:00
2 2009-01-01 12:00:00
3 2009-01-07 12:00:00
4 2009-01-06 12:00:00
5 2009-01-03 12:00:00
6 2009-01-09 12:00:00
7 2009-01-15 12:00:00



数据如上

我想求出现有时间的差。
比如最晚一天时间与ID=3的时间天数差，最晚是2009-01-15 而ID=3是 2009-01-07
时间差就应该是3天

这个不能用datediff 因为…
[/Quote]
最晚是2009-01-15 而ID=3是 2009-01-07
时间差就应该是3天
为什么是3天

[HarleyTung]

结果就是返回3就行了

[HarleyTung]

当然是3天啊，在2009-01-07与2009-01-15 之间只有 1 2009-01-08 12:00:00
和 6 2009-01-09 12:00:00
所以就是3天。

[水族杰纶]

if object_id('tb','U')is not null drop table tb
go
create table tb(ID int,CreateTime datetime)
insert tb select 1       ,'2009-01-08 12:00:00'
insert tb select 2      , '2009-01-01 12:00:00'
insert tb select 3     ,  '2009-01-07 12:00:00'
insert tb select 4    ,   '2009-01-06 12:00:00'
insert tb select 5   ,    '2009-01-03 12:00:00'
insert tb select 6  ,     '2009-01-09 12:00:00'
insert tb select 7 ,      '2009-01-15 12:00:00'
select count(*)-1 时间间隔 from tb where CreateTime
between (select min(CreateTime)CreateTime from tb where id=3) and
                     (select max(CreateTime)CreateTime from tb)
/*时间间隔
-----------
3*/

[HarleyTung]

能不能对应列出所有的天数差呢，比如结果为

ID      CreateTime              DayCount
1       2009-01-08 12:00:00       2
2       2009-01-01 12:00:00       7
3       2009-01-07 12:00:00       3
4       2009-01-06 12:00:00       4
5       2009-01-03 12:00:00       5
6       2009-01-09 12:00:00       1
7       2009-01-15 12:00:00       0

就查询出这样的结果就可以了，解决立刻结贴。谢谢！

[水族杰纶]

[Quote=引用 6 楼 dyjqk 的回复:]
能不能对应列出所有的天数差呢，比如结果为


SQL code
ID CreateTime DayCount
1 2009-01-08 12:00:00 2
2 2009-01-01 12:00:00 7
3 2009-01-07 12:00:00 3
4 2009-01-06 12:00:00 4
5 2009-01-03 12:00:00 5
6 2009-01-09 12:00:00 1
7 2009-01-15 12:00:00 0





就查询出这样的结果就可以了，解…
[/Quote]

if object_id('tb','U')is not null drop table tb
go
create table tb(ID int,CreateTime datetime)
insert tb select 1       ,'2009-01-08 12:00:00'
insert tb select 2      , '2009-01-01 12:00:00'
insert tb select 3     ,  '2009-01-07 12:00:00'
insert tb select 4    ,   '2009-01-06 12:00:00'
insert tb select 5   ,    '2009-01-03 12:00:00'
insert tb select 6  ,     '2009-01-09 12:00:00'
insert tb select 7 ,      '2009-01-15 12:00:00'
select t.*,(select count(*)-1 from tb where CreateTime>=t.CreateTime)DayCount from tb t
/*ID          CreateTime              DayCount
----------- ----------------------- -----------
1           2009-01-08 12:00:00.000 2
2           2009-01-01 12:00:00.000 6
3           2009-01-07 12:00:00.000 3
4           2009-01-06 12:00:00.000 4
5           2009-01-03 12:00:00.000 5
6           2009-01-09 12:00:00.000 1
7           2009-01-15 12:00:00.000 0*/

[npkaida]

declare @tb table([ID] int,[CreateTime] datetime)
insert @tb
select 1,'2009-01-08 12:00:00' union all
select 2,'2009-01-01 12:00:00' union all
select 3,'2009-01-07 12:00:00' union all
select 4,'2009-01-06 12:00:00' union all
select 5,'2009-01-03 12:00:00' union all
select 6,'2009-01-09 12:00:00' union all
select 7,'2009-01-15 12:00:00'

select *,
 datediff(d, [CreateTime], (select max([CreateTime]) from @tb)) [时间差(天)] 
 from @tb


--测试结果:
/*
ID CreateTime              时间差(天)
1  2009-01-08 12:00:00.000 7
2  2009-01-01 12:00:00.000 14
3  2009-01-07 12:00:00.000 8
4  2009-01-06 12:00:00.000 9
5  2009-01-03 12:00:00.000 12
6  2009-01-09 12:00:00.000 6
7  2009-01-15 12:00:00.000 0
*/