时间间隔/分组
表格如下：
time
9:05
9:10
9:11
9:15
...
想得到以下结果集
start end
9:05 9:05
9:10 9:11
9:15 9:15
间隔 <2分钟，归为一组
[解决办法]
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 '
select tmp.[start], [end]=max(tmp.[time])
from
(
select A.[time], [start]=min(B.[time])
from T as A, T as B
where datediff(minute, convert(datetime, B.[time], 108), convert(datetime, A.[time], 108)) <2
group by A.[time]
)tmp
group by tmp.[start]
--result
start end
---------- ----------
9:05 9:05
9:10 9:11
9:15 9:15
(3 row(s) affected)
