-
本版专家分：0结帖率 100% -
我的字段和内容如下：
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
请各位帮忙，谢谢。![]()
-
本版专家分：11697 -
select * from tab a
where not ((select top 1 flag from tab where actiontime > a.actiontime order by actiontime
)=a.flag)
-
本版专家分：11983
-
--> 测试数据: [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 行受影响)
-
本版专家分：11396
-
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
*/
-
本版专家分：5507 -
怎么区分flag=0的记录。有两段
-
本版专家分：11983
-
2次分组，思路不错
![]()
-
本版专家分：61563
-
-
微软MVP
2016年4月荣获微软MVP称号
2015年4月荣获微软MVP称号
2014年4月 荣获微软MVP称号
2013年4月 荣获微软MVP称号
-
- 黄花 2013年4月 MS-SQL Server大版内专家分月排行榜第二
-
- 蓝花 2012年5月 MS-SQL Server大版内专家分月排行榜第三
-
-
+
-
本版专家分：2076 -
学习了
