求一SQL语句的写法

酒红色的泪 2011-09-21 04:14:24
我的字段和内容如下:
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

请各位帮忙,谢谢。
...全文
109 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
q465897859 2011-09-21
  • 打赏
  • 举报
回复
学习了
酒红色的泪 2011-09-21
  • 打赏
  • 举报
回复
多谢几位的帮助,本人对数据库的知道了解不多,这几条语句够我研究一阵了~
Mr_Nice 2011-09-21
  • 打赏
  • 举报
回复
[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]


+
geniuswjt 2011-09-21
  • 打赏
  • 举报
回复
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]
areswang 2011-09-21
  • 打赏
  • 举报
回复
怎么区分flag=0的记录。有两段
gw6328 2011-09-21
  • 打赏
  • 举报
回复

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
*/
geniuswjt 2011-09-21
  • 打赏
  • 举报
回复

--> 测试数据: [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 行受影响)
NBDBA 2011-09-21
  • 打赏
  • 举报
回复
select * from tab a
where not ((select top 1 flag from tab where actiontime > a.actiontime order by actiontime
)=a.flag)

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧