create view v1 as select * from tbl a where not exists (select 1 from tbl where a.db0 = db0 and a.TriggerTime>TriggerTime) go create view v2 as select * from tbl a where not exists (select 1 from tbl where a.db1 = db0 and a.TriggerTime<TriggerTime)
create view v1 as select * from tbl a where not exists (select 1 from tbl where a.db0 = db0 and dateadd(ss,-1,a.TriggerTime)=TriggerTime) go create view v2 as select * from tbl a where not exists (select 1 from tbl where a.db1 = db1 and dateadd(ss,-1,a.TriggerTime)=TriggerTime)
LZ 如果你的设备记录时间间隔为1秒,xiaoku 的是可以的。如果你的设备记录时间间隔为n秒,将dateadd(ss,-1,a.TriggerTime)=TriggerTime) 改为dateadd(ss,-n,a.TriggerTime)=TriggerTime) xiaoku的也是可以的。但如果你的设备记录时间间隔不固定(只要不为0),那就要用下面的方法: --DB0 select * from tbl a where not exists (select 1 from tbl where a.db0 = db0 and Triggertime = (select max(triggertime) from tbl where triggertime<triggertime)) --DB1 select * from tbl a where not exists (select 1 from tbl where a.db1 = db1 and Triggertime = (select max(triggertime) from tbl where triggertime<triggertime))
create view v1
as
select *
from tbl a
where not exists (select 1 from tbl where a.db0 = db0 and a.TriggerTime>TriggerTime)
go
create view v2
as
select *
from tbl a
where not exists (select 1 from tbl where a.db1 = db0 and a.TriggerTime<TriggerTime)
create view v1
as
select *
from tbl a
where not exists (select 1 from tbl where a.db0 = db0 and dateadd(ss,-1,a.TriggerTime)=TriggerTime)
go
create view v2
as
select *
from tbl a
where not exists (select 1 from tbl where a.db1 = db1 and dateadd(ss,-1,a.TriggerTime)=TriggerTime)
1(2008-1-12 12:01:01 )->2(2008-1-12 12:01:03)-> 1(2008-1-12 12:01:04) 我的库是一个记录设备随时间而变化的动作记录库。提取的时候希望按照各数据的变化趋势拆分,就是上面的例子。
而从逻辑关系讲就是不希望得到连续而且重复的值,例如:
DB0,TriggerTime
1,2008-1-12 12:01:01
1,2008-1-12 12:01:02
这就是一组值DB0没发生变化,只有时间发生变化的值。
我希望的提取到的是DB0发生变化边沿的值
就是这样
请问tbl 跟 a 指代什么啊
试过了,不可以
如果你的设备记录时间间隔为1秒,xiaoku 的是可以的。如果你的设备记录时间间隔为n秒,将dateadd(ss,-1,a.TriggerTime)=TriggerTime)
改为dateadd(ss,-n,a.TriggerTime)=TriggerTime) xiaoku的也是可以的。但如果你的设备记录时间间隔不固定(只要不为0),那就要用下面的方法:
--DB0
select * from tbl a where not exists (select 1 from tbl where a.db0 = db0
and Triggertime = (select max(triggertime) from tbl where triggertime<triggertime))
--DB1
select * from tbl a where not exists (select 1 from tbl where a.db1 = db1
and Triggertime = (select max(triggertime) from tbl where triggertime<triggertime))
我用的是SQL 2005 Express
我确实把上面代码输进去了但是确实不行
到下面的结果:
DB0, TriggerTime
1 2008-01-12 12:20:20.000
1 2008-01-12 12:20:22.000
2 2008-01-12 12:20:30.000
1 2008-01-12 12:20:40.000