【问个查询问题】oracle数据查询

[Tz_Sword]

有一组动态数据 10s存一次数据库
表 move

id(自增ID),sid(编号ID),time(sysdate)

记录每隔10秒 存一次数据库
就像公交车一样

下面是模拟20秒的数据插入情况
1-10s
1,A,...
2,B,...
3,C,....

10-20s
4,C,...
5,B,...
6,A,...

怎么查询出这样的一组数据呢

[zhouzhexialei]

如果是按10S一个时间段来统计数据的话，可参照如下脚本： select to_char(trunc(the_date)+trunc(to_char(the_date,'sssss')/10)/8640,'hh24:mi:ss') period, count(1) the_num from table where the_date>=date'2012-11-1' and the_date<date'2012-11-2' group by to_char(trunc(the_date)+trunc(to_char(the_date,'sssss')/10)/8640,'hh24:mi:ss'); 日期可以自己修改。 其中，to_char(the_date,'sssss')是指将具体的日期的时分秒部分都换算成秒数，/10是指看这个秒数折合成多少个10秒，/8640是指看具体某一个10秒是在当天的第几个10秒上（8640是指一天有8640个10秒）。

[Tz_Sword]

就是根据 sid 取最新的数据 10秒内的数据

[Tz_Sword]

select * from table t where t.sid=XX and time<10秒 and后面小于10秒怎么写

[Tz_Sword]

取最近的10秒内的数据

[小海葵1]

with test as ( select 'A' as col_a , '2012-11-23 14:38:20' as col_b from dual union all select 'B' as col_a , '2012-11-23 14:38:28' as col_b from dual union all select 'C' as col_a , '2012-11-23 14:38:30' as col_b from dual union all select 'D' as col_a , '2012-11-23 14:38:31' as col_b from dual union all select 'D' as col_a , '2012-11-23 14:38:29' as col_b from dual ) SELECT col_a,col_b FROM ( SELECT col_a,col_b,TO_CHAR(TO_DATE(col_b,'YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHHMISS') AS col_bNUM , MIN(TO_CHAR(TO_DATE(col_b,'YYYY-MM-DD HH24:MI:SS'),'YYYYMMDDHHMISS')) keep (DENSE_RANK FIRST ORDER BY col_b DESC) over(partition by NULL) AS STR FROM TEST ORDER BY COL_B DESC) WHERE STR-col_bNUM <11

[流浪川]

BETWEEN SYSDATE-10/(24*60*60) AND SYSDATE 不行么？

[流浪川]

select * from table t where t.sid=XX and time BETWEEN SYSDATE-10/(24*60*60) AND SYSDATE

[软件钢琴师]

select id,sid from move order by sysdate,id

[fatfoxz]

没太看明白需求

[开着拖拉机泡妞]

没明白你到底什么个意思

[小海葵1]

是不是按每10S分组查询呀？

[zhouzhexialei]

如果是需要每隔10S分组查询的话，可以直接对time字段to_char(time,'sssss')。