求一SQL语句的写法

[酒红色的泪]

我的字段和内容如下：
flag actiontime
0 2011-09-21 12:00:00
0 2011-09-21 12:02:00
0 2011-09-21 12:03:02
1 2011-09-21 12:04:00
1 2011-09-21 12:05:00
1 2011-09-21 12:06:00
1 2011-09-21 12:07:00
0 2011-09-21 12:08:00
0 2011-09-21 12:09:00

我现在需要查询flag是0的起止时间，即时间段和flag是1的时间段。
结果应该如下：

0 2011-09-21 12:00:00 2011-09-21 12:03:02
1 2011-09-21 12:04:00 2011-09-21 12:07:00
0 2011-09-21 12:08:00 2011-09-21 12:09:00

请各位帮忙，谢谢。

[NBDBA]

select * from tab a
where not ((select top 1 flag from tab where actiontime > a.actiontime order by actiontime
)=a.flag)

[geniuswjt]

--> 测试数据: [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 行受影响)

[gw6328]

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
*/

[areswang]

怎么区分flag=0的记录。有两段

[geniuswjt]

2次分组，思路不错[Quote=引用 3 楼 jinfengyiye 的回复:]
SQL code

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:0……
[/Quote]

[Mr_Nice]

[Quote=引用 3 楼 jinfengyiye 的回复:]

SQL code

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……
[/Quote]


+

[酒红色的泪]

多谢几位的帮助，本人对数据库的知道了解不多，这几条语句够我研究一阵了~

[q465897859]

学习了