--> 测试数据: [tb]
if object_id('[tb]') is not null drop table [tb]
create table [tb] (flag int,actiontime datetime)
insert into [tb]
select 0,'2011-09-21 12:00:00' union all
select 0,'2011-09-21 12:02:00' union all
select 0,'2011-09-21 12:03:02' union all
select 1,'2011-09-21 12:04:00' union all
select 1,'2011-09-21 12:05:00' union all
select 1,'2011-09-21 12:06:00' union all
select 1,'2011-09-21 12:07:00' union all
select 0,'2011-09-21 12:08:00' union all
select 0,'2011-09-21 12:09:00'
--开始查询
;with cte(flag,t1,t2) as(
select flag,MIN(actiontime),MAX(actiontime) from tb where flag=1 group by flag
)
select flag,MIN(actiontime) begintime,MAX(actiontime) endtime from tb a
where exists(select 1 from cte where t1>a.actiontime)
group by flag
union all
select flag,t1,t2 from cte
union all
select flag,MIN(actiontime),MAX(actiontime) from tb a
where exists(select 1 from cte where t2<a.actiontime)
group by flag
--结束查询
drop table [tb]
/* 其实我这个不是个好方案,如果分N次就杯具了,不过也没想出别的,唉,菜啊
flag begintime endtime
----------- ----------------------- -----------------------
0 2011-09-21 12:00:00.000 2011-09-21 12:03:02.000
1 2011-09-21 12:04:00.000 2011-09-21 12:07:00.000
0 2011-09-21 12:08:00.000 2011-09-21 12:09:00.000
(3 行受影响)