34,575
社区成员
发帖
与我相关
我的任务
分享
declare @t table(flag int,actiontime time)
insert into @t (flag,actiontime)
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'
--select * from @t;
;with cte as(
select rn=ROW_NUMBER() over(order by actiontime),rx=ROW_NUMBER() over(PARTITION by flag order by actiontime)
,* from @t
)
select flag,MIN(actiontime) t1,MAX(actiontime) t2 from cte
group by rn-rx,flag order by t1
/*
flag t1 t2
----------- ---------------- ----------------
0 12:00:00.0000000 12:03:02.0000000
1 12:04:00.0000000 12:07:00.0000000
0 12:08:00.0000000 12:09:00.0000000
*/
--> 测试数据: [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 行受影响)
select * from tab a
where not ((select top 1 flag from tab where actiontime > a.actiontime order by actiontime
)=a.flag)