有一个查询某个人某段时间轨迹的功能,现在用sql查出来的记录放在一个临时表@abc里,结构如下:--senid:天线编号,personCode:人员编号,readtime:读取时间,sensorType:天线类型(2:出井,1:井下,0:入井)
declare @abc table (
senid int ,
personCode varchar(20) NOT NULL ,
readtime datetime NULL ,
sensorType int
)
insert into @abc select 58,'3111','2009-3-18 6:00',2
insert into @abc select 58,'3111','2009-3-18 7:00',2
insert into @abc select 58,'3111','2009-3-18 8:00',2
insert into @abc select 58,'3111','2009-3-18 9:00',2
insert into @abc select 60,'3111','2009-3-18 10:00',0
insert into @abc select 60,'3111','2009-3-18 11:00',0
insert into @abc select 61,'3111','2009-3-18 11:30',1
insert into @abc select 61,'3111','2009-3-18 12:00',1
insert into @abc select 62,'3111','2009-3-18 12:30',1
insert into @abc select 62,'3111','2009-3-18 13:00',1
insert into @abc select 60,'3111','2009-3-18 13:30',0
insert into @abc select 60,'3111','2009-3-18 14:00',0
insert into @abc select 58,'3111','2009-3-18 14:30',2
insert into @abc select 58,'3111','2009-3-18 15:00',2
insert into @abc select 58,'3111','2009-3-18 16:00',2现在想只取该人在出井天线读到的信息1次,上面例子中就是只取58号天线读到的最初一条和最后一条,其余的删除掉,最终结果类似于58,'3111','2009-3-18 6:00',2
60,'3111','2009-3-18 10:00',0
60,'3111','2009-3-18 11:00',0
61,'3111','2009-3-18 11:30',1
61,'3111','2009-3-18 12:00',1
62,'3111','2009-3-18 12:30',1
60,'3111','2009-3-18 13:30',0
60,'3111','2009-3-18 14:00',0
58,'3111','2009-3-18 16:00',2
declare @abc table (
senid int ,
personCode varchar(20) NOT NULL ,
readtime datetime NULL ,
sensorType int
)
insert into @abc select 58,'3111','2009-3-18 6:00',2
insert into @abc select 58,'3111','2009-3-18 7:00',2
insert into @abc select 58,'3111','2009-3-18 8:00',2
insert into @abc select 58,'3111','2009-3-18 9:00',2
insert into @abc select 60,'3111','2009-3-18 10:00',0
insert into @abc select 60,'3111','2009-3-18 11:00',0
insert into @abc select 61,'3111','2009-3-18 11:30',1
insert into @abc select 61,'3111','2009-3-18 12:00',1
insert into @abc select 62,'3111','2009-3-18 12:30',1
insert into @abc select 62,'3111','2009-3-18 13:00',1
insert into @abc select 60,'3111','2009-3-18 13:30',0
insert into @abc select 60,'3111','2009-3-18 14:00',0
insert into @abc select 58,'3111','2009-3-18 14:30',2
insert into @abc select 58,'3111','2009-3-18 15:00',2
insert into @abc select 58,'3111','2009-3-18 16:00',2现在想只取该人在出井天线读到的信息1次,上面例子中就是只取58号天线读到的最初一条和最后一条,其余的删除掉,最终结果类似于58,'3111','2009-3-18 6:00',2
60,'3111','2009-3-18 10:00',0
60,'3111','2009-3-18 11:00',0
61,'3111','2009-3-18 11:30',1
61,'3111','2009-3-18 12:00',1
62,'3111','2009-3-18 12:30',1
60,'3111','2009-3-18 13:30',0
60,'3111','2009-3-18 14:00',0
58,'3111','2009-3-18 16:00',2
declare @abc table (
senid int ,
personCode varchar(20) NOT NULL ,
readtime datetime NULL ,
sensorType int
)
insert into @abc select 58,'3111','2009-3-18 6:00',2
insert into @abc select 58,'3111','2009-3-18 7:00',2
insert into @abc select 58,'3111','2009-3-18 8:00',2
insert into @abc select 58,'3111','2009-3-18 9:00',2
insert into @abc select 60,'3111','2009-3-18 10:00',0
insert into @abc select 60,'3111','2009-3-18 11:00',0
insert into @abc select 61,'3111','2009-3-18 11:30',1
insert into @abc select 61,'3111','2009-3-18 12:00',1
insert into @abc select 62,'3111','2009-3-18 12:30',1
insert into @abc select 62,'3111','2009-3-18 13:00',1
insert into @abc select 60,'3111','2009-3-18 13:30',0
insert into @abc select 60,'3111','2009-3-18 14:00',0
insert into @abc select 58,'3111','2009-3-18 14:30',2
insert into @abc select 58,'3111','2009-3-18 15:00',2
insert into @abc select 58,'3111','2009-3-18 16:00',2
set nocount off
select * from @abc a where not exists(select 1 from @abc where senid=a.senid and datediff(mi,0,readtime)<datediff(mi,0,a.readtime))
union
select * from @abc a where not exists(select 1 from @abc where senid=a.senid and datediff(mi,0,readtime)>datediff(mi,0,a.readtime))senid personCode readtime sensorType
----------- -------------------- ----------------------- -----------
58 3111 2009-03-18 06:00:00.000 2
58 3111 2009-03-18 16:00:00.000 2
60 3111 2009-03-18 10:00:00.000 0
60 3111 2009-03-18 14:00:00.000 0
61 3111 2009-03-18 11:30:00.000 1
61 3111 2009-03-18 12:00:00.000 1
62 3111 2009-03-18 12:30:00.000 1
62 3111 2009-03-18 13:00:00.000 1(8 行受影响)
declare @abc table (
senid int ,
personCode varchar(20) NOT NULL ,
readtime datetime NULL ,
sensorType int
)
insert into @abc select 58,'3111','2009-3-18 6:00',2
insert into @abc select 58,'3111','2009-3-18 7:00',2
insert into @abc select 58,'3111','2009-3-18 8:00',2
insert into @abc select 58,'3111','2009-3-18 9:00',2
insert into @abc select 60,'3111','2009-3-18 10:00',0
insert into @abc select 60,'3111','2009-3-18 11:00',0
insert into @abc select 61,'3111','2009-3-18 11:30',1
insert into @abc select 61,'3111','2009-3-18 12:00',1
insert into @abc select 62,'3111','2009-3-18 12:30',1
insert into @abc select 62,'3111','2009-3-18 13:00',1
insert into @abc select 60,'3111','2009-3-18 13:30',0
insert into @abc select 60,'3111','2009-3-18 14:00',0
insert into @abc select 58,'3111','2009-3-18 14:30',2
insert into @abc select 58,'3111','2009-3-18 15:00',2
insert into @abc select 58,'3111','2009-3-18 16:00',2
set nocount off
select * from @abc a where not exists(select 1 from @abc where senid=a.senid and readtime<a.readtime)
union
select * from @abc a where not exists(select 1 from @abc where senid=a.senid and readtime>a.readtime)senid personCode readtime sensorType
----------- -------------------- ----------------------- -----------
58 3111 2009-03-18 06:00:00.000 2
58 3111 2009-03-18 16:00:00.000 2
60 3111 2009-03-18 10:00:00.000 0
60 3111 2009-03-18 14:00:00.000 0
61 3111 2009-03-18 11:30:00.000 1
61 3111 2009-03-18 12:00:00.000 1
62 3111 2009-03-18 12:30:00.000 1
62 3111 2009-03-18 13:00:00.000 1(8 行受影响)可以直接比较时间
declare @abc table (
senid int ,
personCode varchar(20) NOT NULL ,
readtime datetime NULL ,
sensorType int
)
insert into @abc select 58,'3111','2009-3-18 6:00',2
insert into @abc select 58,'3111','2009-3-18 7:00',2
insert into @abc select 58,'3111','2009-3-18 8:00',2
insert into @abc select 58,'3111','2009-3-18 9:00',2
insert into @abc select 60,'3111','2009-3-18 10:00',0
insert into @abc select 60,'3111','2009-3-18 11:00',0
insert into @abc select 61,'3111','2009-3-18 11:30',1
insert into @abc select 61,'3111','2009-3-18 12:00',1
insert into @abc select 62,'3111','2009-3-18 12:30',1
insert into @abc select 62,'3111','2009-3-18 13:00',1
insert into @abc select 60,'3111','2009-3-18 13:30',0
insert into @abc select 60,'3111','2009-3-18 14:00',0
insert into @abc select 58,'3111','2009-3-18 14:30',2
insert into @abc select 58,'3111','2009-3-18 15:00',2
insert into @abc select 58,'3111','2009-3-18 16:00',2
delete a from @abc a
where exists(select 1 from @abc where senid=a.senid and readtime<a.readtime)
and
exists(select 1 from @abc where senid=a.senid and readtime>a.readtime)select * from @abcsenid personCode readtime sensorType
----------- -------------------- ----------------------- -----------
58 3111 2009-03-18 06:00:00.000 2
60 3111 2009-03-18 10:00:00.000 0
61 3111 2009-03-18 11:30:00.000 1
61 3111 2009-03-18 12:00:00.000 1
62 3111 2009-03-18 12:30:00.000 1
62 3111 2009-03-18 13:00:00.000 1
60 3111 2009-03-18 14:00:00.000 0
58 3111 2009-03-18 16:00:00.000 2
senid int ,
personCode varchar(20) NOT NULL ,
readtime datetime NULL ,
sensorType int
)
insert into @abc select 58,'3111','2009-3-18 6:00',2
insert into @abc select 58,'3111','2009-3-18 7:00',2
insert into @abc select 58,'3111','2009-3-18 8:00',2
insert into @abc select 58,'3111','2009-3-18 9:00',2
insert into @abc select 60,'3111','2009-3-18 10:00',0
insert into @abc select 60,'3111','2009-3-18 11:00',0
insert into @abc select 61,'3111','2009-3-18 11:30',1
insert into @abc select 61,'3111','2009-3-18 12:00',1
insert into @abc select 62,'3111','2009-3-18 12:30',1
insert into @abc select 62,'3111','2009-3-18 13:00',1
insert into @abc select 60,'3111','2009-3-18 13:30',0
insert into @abc select 60,'3111','2009-3-18 14:00',0
insert into @abc select 58,'3111','2009-3-18 14:30',2
insert into @abc select 58,'3111','2009-3-18 15:00',2
insert into @abc select 58,'3111','2009-3-18 16:00',2delete t from @abc t,(select senid,personCode,begintime=min(readtime),endtime=max(readtime),sensorType from @abc group by senid,personCode,sensorType) K
where t.senid=k.senid and t.personCode=k.personCode and t.sensorType=k.sensorType and readtime>begintime and readtime<endtimeselect * from @abc58 3111 2009-03-18 06:00:00.000 2
60 3111 2009-03-18 10:00:00.000 0
61 3111 2009-03-18 11:30:00.000 1
61 3111 2009-03-18 12:00:00.000 1
62 3111 2009-03-18 12:30:00.000 1
62 3111 2009-03-18 13:00:00.000 1
60 3111 2009-03-18 14:00:00.000 0
58 3111 2009-03-18 16:00:00.000 2
senid int ,
personCode varchar(20) NOT NULL ,
readtime datetime NULL ,
sensorType int
)
insert into @abc select 58,'3111','2009-3-18 6:00',2
insert into @abc select 58,'3111','2009-3-18 7:00',2
insert into @abc select 58,'3111','2009-3-18 8:00',2
insert into @abc select 58,'3111','2009-3-18 9:00',2
insert into @abc select 60,'3111','2009-3-18 10:00',0
insert into @abc select 60,'3111','2009-3-18 11:00',0
insert into @abc select 61,'3111','2009-3-18 11:30',1
insert into @abc select 61,'3111','2009-3-18 12:00',1
insert into @abc select 62,'3111','2009-3-18 12:30',1
insert into @abc select 62,'3111','2009-3-18 13:00',1
insert into @abc select 60,'3111','2009-3-18 13:30',0
insert into @abc select 60,'3111','2009-3-18 14:00',0
insert into @abc select 58,'3111','2009-3-18 14:30',2
insert into @abc select 58,'3111','2009-3-18 15:00',2
insert into @abc select 58,'3111','2009-3-18 16:00',2
delete a from @abc a
where exists(select 1 from @abc where senid=a.senid and readtime<a.readtime)
and
exists(select 1 from @abc where senid=a.senid and readtime>a.readtime)