wellname,gettime,state
X1-1-26 2011-09-01 01:32:00.000 0
X1-1-26 2011-09-01 01:42:00.000 0
X1-1-26 2011-09-01 00:31:54.000 1
X1-1-26 2011-09-01 00:41:55.000 1
X1-1-26 2011-09-01 00:11:51.000 1
X1-1-26 2011-09-01 00:21:53.000 1
X1-1-26 2011-09-01 00:01:50.000 0
X1-1-E920 2011-09-01 00:08:01.000 1
X1-1-E920 2011-09-01 00:18:04.000 1
X1-1-E920 2011-09-01 00:28:05.000 1
X1-1-E920 2011-09-01 00:38:06.000 1
X1-1-E920 2011-09-01 00:48:14.000 0
X1-1-E920 2011-09-01 00:58:11.000 0
X1-1-E920 2011-09-01 01:08:11.000 0
请问如何查询得出以下结果?
wellname,state,starttime,endtime
X1-1-26 1 2011-09-01 00:31:54 2011-09-01 00:21:53
X1-1-E920 1 2011-09-01 00:08:01 2011-09-01 00:38:06
X1-1-26 2011-09-01 01:32:00.000 0
X1-1-26 2011-09-01 01:42:00.000 0
X1-1-26 2011-09-01 00:31:54.000 1
X1-1-26 2011-09-01 00:41:55.000 1
X1-1-26 2011-09-01 00:11:51.000 1
X1-1-26 2011-09-01 00:21:53.000 1
X1-1-26 2011-09-01 00:01:50.000 0
X1-1-E920 2011-09-01 00:08:01.000 1
X1-1-E920 2011-09-01 00:18:04.000 1
X1-1-E920 2011-09-01 00:28:05.000 1
X1-1-E920 2011-09-01 00:38:06.000 1
X1-1-E920 2011-09-01 00:48:14.000 0
X1-1-E920 2011-09-01 00:58:11.000 0
X1-1-E920 2011-09-01 01:08:11.000 0
请问如何查询得出以下结果?
wellname,state,starttime,endtime
X1-1-26 1 2011-09-01 00:31:54 2011-09-01 00:21:53
X1-1-E920 1 2011-09-01 00:08:01 2011-09-01 00:38:06
解决方案 »
- 高分求教SQL存储过程..
- 昨天问过一次了http://topic.csdn.net/u/20080317/02/8aae1c4b-1d8d-4022-9661-e669504d006c.html
- 奇怪了,isnull(a,0)与case when a is null then 1 else 0 end
- 一个简单的SQL语句问题,主要是取当前日期
- 急,真的很急!!!update一条语句,执行结果完成拉,但数据居然没有修改,什么原因啊
- sql 两行数据合并成一行显示
- 求一SQL函数...
- 这个错误是怎么产生的"[OLE/DB provider returned message: 无法启动应用程序。工作组信息文件丢失,或是已被其它用户以独占方式打开。]"
- 数据库连接出错,有谁遇到给解决下?
- 排序规则使用Latin1_General_100,会影响简体中文的使用吗?
- 并发操作怎么保证数据有效
- 救简单的SQL查询时间段语句
select wellname,state,min(gettime) 'starttime',max(gettime) 'endtime'
from tab
where state=1
group by wellname,state
insert into tb select 'X1-1-26','2011-09-01 01:32:00.000',0
insert into tb select 'X1-1-26','2011-09-01 01:42:00.000',0
insert into tb select 'X1-1-26','2011-09-01 00:31:54.000',1
insert into tb select 'X1-1-26','2011-09-01 00:41:55.000',1
insert into tb select 'X1-1-26','2011-09-01 00:11:51.000',1
insert into tb select 'X1-1-26','2011-09-01 00:21:53.000',1
insert into tb select 'X1-1-26','2011-09-01 00:01:50.000',0
insert into tb select 'X1-1-E920','2011-09-01 00:08:01.000',1
insert into tb select 'X1-1-E920','2011-09-01 00:18:04.000',1
insert into tb select 'X1-1-E920','2011-09-01 00:28:05.000',1
insert into tb select 'X1-1-E920','2011-09-01 00:38:06.000',1
insert into tb select 'X1-1-E920','2011-09-01 00:48:14.000',0
insert into tb select 'X1-1-E920','2011-09-01 00:58:11.000',0
insert into tb select 'X1-1-E920','2011-09-01 01:08:11.000',0
go
;with c1 as(
select *,row_number()over(partition by wellname order by gettime)rn from tb
),c2 as(
select *,gettime dt from c1 a where state=1 and not exists(select 1 from c1 where wellname=a.wellname and gettime<a.gettime and state=1)
union all
select a.*,b.dt from c1 a inner join c2 b on a.wellname=b.wellname and a.rn=b.rn+1 and a.state=1 and b.state=1
)select wellname,dt as statetime,gettime as endtime
from c2 a where not exists(select 1 from c2 where wellname=a.wellname and dt=a.dt and gettime>a.gettime)
/*
wellname statetime endtime
---------- ----------------------- -----------------------
X1-1-E920 2011-09-01 00:08:01.000 2011-09-01 00:38:06.000
X1-1-26 2011-09-01 00:11:51.000 2011-09-01 00:41:55.000(2 行受影响)*/
go
drop table tb
select wellname,state,min(gettime) as starttime,max(gettime) as endtime
from tb
where state=1
group by wellname,state
create table tb(wellname varchar(10),gettime datetime,state int)
insert into tb select 'X1-1-26','2011-09-01 01:32:00.000',0
insert into tb select 'X1-1-26','2011-09-01 01:42:00.000',0
insert into tb select 'X1-1-26','2011-09-01 00:31:54.000',1
insert into tb select 'X1-1-26','2011-09-01 00:41:55.000',1
insert into tb select 'X1-1-26','2011-09-01 00:11:51.000',1
insert into tb select 'X1-1-26','2011-09-01 00:21:53.000',1
insert into tb select 'X1-1-26','2011-09-01 00:01:50.000',0
insert into tb select 'X1-1-E920','2011-09-01 00:08:01.000',1
insert into tb select 'X1-1-E920','2011-09-01 00:18:04.000',1
insert into tb select 'X1-1-E920','2011-09-01 00:28:05.000',1
insert into tb select 'X1-1-E920','2011-09-01 00:38:06.000',1
insert into tb select 'X1-1-E920','2011-09-01 00:48:14.000',0
insert into tb select 'X1-1-E920','2011-09-01 00:58:11.000',0
insert into tb select 'X1-1-E920','2011-09-01 01:08:11.000',0
select wellname,state,min(gettime) 'starttime',max(gettime) 'endtime'
from tb
where state=1
group by wellname,state
wellname state starttime endtime
---------- ----------- ----------------------- -----------------
X1-1-26 1 2011-09-01 00:11:51 2011-09-01 00:41:55
X1-1-E920 1 2011-09-01 00:08:01 2011-09-01 00:38:06
是不是就是要同个wellname且state=1的最大最小时间?
select wellname,state,min(gettime) starttime,max(gettime) endtime
from tb
where state=1
group by wellname,state/*
wellname state starttime endtime
---------- ----------- ----------------------- -----------------
X1-1-26 1 2011-09-01 00:11:51 2011-09-01 00:41:55
X1-1-E920 1 2011-09-01 00:08:01 2011-09-01 00:38:06