如何实现按时间间隔查询数据（急）
表 T_SIGNALid create_time value------ ------------- ---- 1 2010-03-01 01:00:00 10 2 2010-03-01 01:03:00 103 2010-03-01 01:04:00 104 2010-03-01 01:05:00 105 2010-03-01 01:07:00 106 2010-03-01 01:09:00 10 7 2010-03-01 01:12:00 10 8 2010-03-01 01:14:00 10 9 2010-03-01 01:25:00 10 10 2010-03-01 02:10:00 10 按时间间隔4分钟从上表中取数据（每条数据的create_time肯定比上一条的的create_time大于或等于4分钟）。取得应结果为：id create_time value------ ------------- ---- 1 2010-03-01 01:00:00 10 3 2010-03-01 01:04:00 106 2010-03-01 01:09:00 10 8 2010-03-01 01:14:00 10 9 2010-03-01 01:25:00 10 10 2010-03-01 02:10:00 10 现用一条SQL语句（oracle）实现，该怎么写呢？
select id ,create_time,value(select id ,create_time,value,lag(create_time) over(order by id) as pre_timefrom T_SIGNAL)where (create_time-pre_time)*24*60>4
[解决办法]
一楼正解，对于这种常规方法不能解决的问题
一般要通过树型结构的语句或者分析函数来处理
关于树型查询的语句start with..connect by可参考
1.http://t.00-1.cn/oracle-01e1a85d6d06f1fd.html
2.http://hi.baidu.com/deepsee/blog/item/67ae46107f4f3ef7c3ce79ea.html
关于分析函可参考
1.http://blog.csdn.net/youjianbo_han_87/archive/2009/06/25/4297867.aspx
2.http://oracle.chinaitlab.com/induction/801521.html
[解决办法]
一楼的写法有点小问题，就是value值如果不一样的话可能会有点问题。
修改了一下写法，如下所示。
还有一个问题，这种写法会等待start with 的树建立完成之后才会过滤每个level的第一个node。好像效率不高。
有没有办法在建立start with的时候就只建立最左边的那一个分支啊。
麻烦高手解答一下。
with t1 as
(
select 1 f1, to_date('2010-03-01 01:00:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 2 f1, to_date('2010-03-01 01:03:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 3 f1, to_date('2010-03-01 01:04:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 4 f1, to_date('2010-03-01 01:05:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 5 f1, to_date('2010-03-01 01:07:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 6 f1, to_date('2010-03-01 01:09:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 7 f1, to_date('2010-03-01 01:12:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 8 f1, to_date('2010-03-01 01:14:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 9 f1, to_date('2010-03-01 01:25:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
union all
select 10 f1, to_date('2010-03-01 02:10:00','YYYY-MM-DD HH24:MI:SS') f2, 10 f3 from dual
)
select f1,f2,f3
from
(
select t1.f1,f2,f3,level,row_number() over(partition by level order by rownum ) N1
from t1
start with f1 =1
connect by f1 > prior f1
and f2 >= prior f2 + 4/24/60
)
where n1 = 1
[解决办法]
with tt as( select 1 id ,to_date('2010-03-01 01:00:00','yyyy-mm-dd hh24:mi:ss')create_time,10 value from dual union all select 2,to_date('2010-03-01 01:03:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 3,to_date('2010-03-01 01:04:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 4,to_date('2010-03-01 01:05:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 5,to_date('2010-03-01 01:07:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 6,to_date('2010-03-01 01:09:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 7,to_date('2010-03-01 01:12:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 8,to_date('2010-03-01 01:14:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 9,to_date('2010-03-01 01:25:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 10,to_date('2010-03-01 02:10:00','yyyy-mm-dd hh24:mi:ss'),10 from dual) select * from tt t start with id = 1connect by prior create_time <= create_time - 4 / 60 / 24 and (select max(create_time) keep(dense_rank last order by id) from tt where id < t.id) < prior create_time + 4 / 60 / 24
[解决办法]
我认为写个过程效率会更高，也更准确点
select * from tt;drop table tt;create table tt as select 1 id ,to_date('2010-03-01 01:00:00','yyyy-mm-dd hh24:mi:ss')create_time,10 value from dual union all select 2,to_date('2010-03-01 01:03:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 3,to_date('2010-03-01 01:04:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 4,to_date('2010-03-01 01:05:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 5,to_date('2010-03-01 01:07:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 6,to_date('2010-03-01 01:09:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 7,to_date('2010-03-01 01:12:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 8,to_date('2010-03-01 01:14:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 9,to_date('2010-03-01 01:25:00','yyyy-mm-dd hh24:mi:ss'),10 from dual union all select 10,to_date('2010-03-01 02:10:00','yyyy-mm-dd hh24:mi:ss'),10 from dual;alter table tt add flag char(1);--添加一列，作为标识--过程declare cursor cur is select create_time, flag from tt order by id for update; last_time date;begin for cur1 in cur loop if last_time is null or cur1.create_time >= last_time + 4 / 60 / 24 then update tt set flag = 1 where current of cur; last_time := cur1.create_time; end if; end loop; commit;end;--结果select * from tt where flag='1' order by id;ID CREATE_TIME VALUE FLAG1 2010-3-1 1:00:00 10 13 2010-3-1 1:04:00 10 16 2010-3-1 1:09:00 10 18 2010-3-1 1:14:00 10 19 2010-3-1 1:25:00 10 110 2010-3-1 2:10:00 10 1