表record中有这样一个sj字段
sj
-------------------
2005-10-29 10:18:49
2005-10-29 10:20:32
2005-10-29 10:22:14
2005-10-29 10:23:56
2005-10-29 10:25:38
2005-10-29 10:27:20
2005-10-29 10:29:02
2005-10-29 10:30:49
2005-10-29 10:33:35
2005-10-29 10:35:00
2005-10-29 10:36:24sj
-------------------
2005-10-29 10:38:40
2005-10-29 10:40:05
2005-10-29 10:41:29
2005-10-29 10:42:54
2005-10-29 10:44:18
2005-10-29 10:45:43
2005-10-29 10:47:08
2005-10-29 10:48:33
2005-10-29 10:49:58
2005-10-29 10:51:23
2005-10-29 10:52:46sj的类型为VARCHAR2(19)
我想获得时间间隔固定的记录,比如:每隔五分钟,十分钟,十五分钟,半小时的记录,该怎么办?
(因为原始数据间隔不是绝对相同的,近似相差四十几秒,间隔近似,不要相差太大即可)
sj
-------------------
2005-10-29 10:18:49
2005-10-29 10:20:32
2005-10-29 10:22:14
2005-10-29 10:23:56
2005-10-29 10:25:38
2005-10-29 10:27:20
2005-10-29 10:29:02
2005-10-29 10:30:49
2005-10-29 10:33:35
2005-10-29 10:35:00
2005-10-29 10:36:24sj
-------------------
2005-10-29 10:38:40
2005-10-29 10:40:05
2005-10-29 10:41:29
2005-10-29 10:42:54
2005-10-29 10:44:18
2005-10-29 10:45:43
2005-10-29 10:47:08
2005-10-29 10:48:33
2005-10-29 10:49:58
2005-10-29 10:51:23
2005-10-29 10:52:46sj的类型为VARCHAR2(19)
我想获得时间间隔固定的记录,比如:每隔五分钟,十分钟,十五分钟,半小时的记录,该怎么办?
(因为原始数据间隔不是绝对相同的,近似相差四十几秒,间隔近似,不要相差太大即可)
-------------------
1 2005-10-29 10:18:49
2 2005-10-29 10:20:32
3 2005-10-29 10:22:14
4 2005-10-29 10:23:56
5 2005-10-29 10:25:38
6 2005-10-29 10:27:20
7 2005-10-29 10:29:02
8 2005-10-29 10:30:49
9 2005-10-29 10:33:35
10 2005-10-29 10:35:00
11 2005-10-29 10:36:24sj
-------------------
12 2005-10-29 10:38:40
13 2005-10-29 10:40:05
14 2005-10-29 10:41:29
15 2005-10-29 10:42:54
16 2005-10-29 10:44:18
17 2005-10-29 10:45:43
18 2005-10-29 10:47:08
19 2005-10-29 10:48:33
20 2005-10-29 10:49:58
21 2005-10-29 10:51:23
22 2005-10-29 10:52:46
我只想获得第1,5,9,13,17...这些记录,该怎么办呢?
select sj from t where rownum mod 4 = 1;
(本来想高分相送,但是无奈囊中羞涩,还望各位别介意,有机会一定补上!)
drop table sj;
create table sj
(
sj nvarchar2(40)
);
insert into sj values('2005-10-29 10:18:49');
insert into sj values('2005-10-29 10:20:32');
insert into sj values('2005-10-29 10:22:14');
insert into sj values('2005-10-29 10:23:56');
insert into sj values('2005-10-29 10:25:38');
insert into sj values('2005-10-29 10:27:20');
insert into sj values('2005-10-29 10:29:02');
insert into sj values('2005-10-29 10:30:49');
insert into sj values('2005-10-29 10:33:35');
insert into sj values('2005-10-29 10:35:00');
insert into sj values('2005-10-29 10:36:24');
insert into sj values('2005-10-29 10:38:40');
insert into sj values('2005-10-29 10:40:05');
insert into sj values('2005-10-29 10:41:29');
insert into sj values('2005-10-29 10:42:54');
insert into sj values('2005-10-29 10:44:18');
insert into sj values('2005-10-29 10:45:43');
insert into sj values('2005-10-29 10:47:08');
insert into sj values('2005-10-29 10:48:33');
insert into sj values('2005-10-29 10:49:58');
insert into sj values('2005-10-29 10:51:23');
insert into sj values('2005-10-29 10:52:46');
commit;
select nn,sj from
(
select rownum nn,sj from sj connect by rownum<22
)
where mod(nn,4)=1--result:1 2005-10-29 10:18:49
5 2005-10-29 10:18:49
9 2005-10-29 10:18:49
13 2005-10-29 10:18:49
17 2005-10-29 10:18:49
21 2005-10-29 10:18:49
不过在result中的sj的结果都是一样的?