34,592
社区成员
发帖
与我相关
我的任务
分享还是时间间隔的问题
表格如下：
time
9:05
9:10
9:11
9:15
...
想得到下面的结果集合：
5
1
4
..
以及最大/小的间隔 5/1
time
9:05
9:10
9:11
9:15
...
想得到下面的结果集合：
5
1
4
..
以及最大/小的间隔 5/1
...全文
请发表友善的回复…
发表回复
marco08 2007-02-07
- 打赏
- 举报
create table T([time] varchar(10))
insert T select '9:05'
union all select '9:10'
union all select '9:11'
union all select '9:15'
go
select
begTime=A.[time],
endTime=min(B.[time]),
间隔=datediff(minute, convert(datetime, A.[time], 108), convert(datetime, min(B.[time]), 108))
from T as A, T as B
where convert(datetime, A.[time], 108)<convert(datetime, B.[time], 108)
group by A.[time]
--result
begTime endTime 间隔
---------- ---------- -----------
9:05 9:10 5
9:10 9:11 1
9:11 9:15 4
insert T select '9:05'
union all select '9:10'
union all select '9:11'
union all select '9:15'
go
select
begTime=A.[time],
endTime=min(B.[time]),
间隔=datediff(minute, convert(datetime, A.[time], 108), convert(datetime, min(B.[time]), 108))
from T as A, T as B
where convert(datetime, A.[time], 108)<convert(datetime, B.[time], 108)
group by A.[time]
--result
begTime endTime 间隔
---------- ---------- -----------
9:05 9:10 5
9:10 9:11 1
9:11 9:15 4
