例子:
表a有3个字段,name,time,type
name time type
1-1234 2011-12-6 10:57:02 1
1-1234 2011-12-6 13:57:02 1
1-1234 2011-12-6 13:57:04 1
1-1234 2011-12-6 13:59:00 2
1-1234 2011-12-6 14:30:00 1
1-2222 2011-12-6 14:32:00 1
1-2222 2011-12-6 14:35:00 4
...
要查询表全部数据,如果是name和type都相同,且time间隔小于5分钟的情况下只取一条(随便或者time的最大那条);
查询结果
name time type
1-1234 2011-12-6 10:57:02 1
1-1234 2011-12-6 13:57:04 1
1-1234 2011-12-6 13:59:00 2
1-1234 2011-12-6 14:30:00 1
1-2222 2011-12-6 14:32:00 1
1-2222 2011-12-6 14:35:00 4
...
表a有3个字段,name,time,type
name time type
1-1234 2011-12-6 10:57:02 1
1-1234 2011-12-6 13:57:02 1
1-1234 2011-12-6 13:57:04 1
1-1234 2011-12-6 13:59:00 2
1-1234 2011-12-6 14:30:00 1
1-2222 2011-12-6 14:32:00 1
1-2222 2011-12-6 14:35:00 4
...
要查询表全部数据,如果是name和type都相同,且time间隔小于5分钟的情况下只取一条(随便或者time的最大那条);
查询结果
name time type
1-1234 2011-12-6 10:57:02 1
1-1234 2011-12-6 13:57:04 1
1-1234 2011-12-6 13:59:00 2
1-1234 2011-12-6 14:30:00 1
1-2222 2011-12-6 14:32:00 1
1-2222 2011-12-6 14:35:00 4
...
SQL> WITH t AS (
2 SELECT '1-1234' t_name,TO_DATE('2011-12-6 10:57:02','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL
3 SELECT '1-1234' t_name,TO_DATE('2011-12-6 13:57:02','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL
4 SELECT '1-1234' t_name,TO_DATE('2011-12-6 13:57:04','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL
5 SELECT '1-1234' t_name,TO_DATE('2011-12-6 13:59:00','yyyy-mm-dd hh24:mi:ss') t_time,2 t_type FROM DUAL UNION ALL
6 SELECT '1-1234' t_name,TO_DATE('2011-12-6 14:30:00','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL
7 SELECT '1-2222' t_name,TO_DATE('2011-12-6 14:32:00','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL
8 SELECT '1-2222' t_name,TO_DATE('2011-12-6 14:35:00','yyyy-mm-dd hh24:mi:ss') t_time,4 t_type FROM DUAL
9 )
10 SELECT m.t_name,
11 TO_CHAR(m.t_time,'yyyy-mm-dd hh24:mi:ss') t_time,
12 m.t_type
13 FROM (
14 SELECT t.*,
15 COUNT(*) OVER(PARTITION BY t.t_name, t_type ORDER BY t.t_time RANGE BETWEEN CURRENT ROW AND INTERVAL '5' minute following) cn
16 FROM t
17 ) m
18 WHERE m.cn = 1
19 ;T_NAME T_TIME T_TYPE
------ ------------------- ----------
1-1234 2011-12-06 10:57:02 1
1-1234 2011-12-06 13:57:04 1
1-1234 2011-12-06 14:30:00 1
1-1234 2011-12-06 13:59:00 2
1-2222 2011-12-06 14:32:00 1
1-2222 2011-12-06 14:35:00 46 rows selected
with tb as (
select '1-1234' as name, to_date('2011-12-6 10:57:02','yyyy-mm-dd hh24:mi:ss') as time, 1 as type from dual union all
select '1-1234' , to_date('2011-12-6 13:57:02','yyyy-mm-dd hh24:mi:ss'), 1 from dual union all
select '1-1234' , to_date(' 2011-12-6 13:57:04','yyyy-mm-dd hh24:mi:ss'), 1 from dual union all
select '1-1234' , to_date(' 2011-12-6 13:59:00','yyyy-mm-dd hh24:mi:ss'), 2 from dual union all
select '1-1234' , to_date(' 2011-12-6 14:30:00','yyyy-mm-dd hh24:mi:ss'), 1 from dual union all
select '1-2222' , to_date(' 2011-12-6 14:32:00','yyyy-mm-dd hh24:mi:ss'), 1 from dual union all
select '1-2222' , to_date(' 2011-12-6 14:35:00','yyyy-mm-dd hh24:mi:ss'), 4 from dual
)
select name,type,time from tb order by name;
--希望得到结果
with tb as (
select '1-1234' as name, to_date('2011-12-6 10:57:02','yyyy-mm-dd hh24:mi:ss') as time, 1 as type from dual union all
select '1-1234' , to_date('2011-12-6 13:57:02','yyyy-mm-dd hh24:mi:ss'), 1 from dual union all
select '1-1234' , to_date(' 2011-12-6 13:57:04','yyyy-mm-dd hh24:mi:ss'), 1 from dual union all
select '1-1234' , to_date(' 2011-12-6 13:59:00','yyyy-mm-dd hh24:mi:ss'), 2 from dual union all
select '1-1234' , to_date(' 2011-12-6 14:30:00','yyyy-mm-dd hh24:mi:ss'), 1 from dual union all
select '1-2222' , to_date(' 2011-12-6 14:32:00','yyyy-mm-dd hh24:mi:ss'), 1 from dual union all
select '1-2222' , to_date(' 2011-12-6 14:35:00','yyyy-mm-dd hh24:mi:ss'), 4 from dual
)
select distinct name,type,to_char(time,'yyyy-mm-dd hh24') || ':' || (ceil(to_char(time,'mi')/5)-1)*5 from tb order by name;
(
NAME VARCHAR2(20),
TIME DATE,
TYPE NUMBER(4)
);INSERT INTO T37 VALUES('1-1234', to_date('2011-12-6 10:57:02', 'YYYY-MM-DD HH24:MI:SS'), 1);
INSERT INTO T37 VALUES('1-1234', to_date('2011-12-6 13:57:02', 'YYYY-MM-DD HH24:MI:SS'), 1);
INSERT INTO T37 VALUES('1-1234', to_date('2011-12-6 13:57:04', 'YYYY-MM-DD HH24:MI:SS'), 1);
INSERT INTO T37 VALUES('1-1234', to_date('2011-12-6 13:59:00', 'YYYY-MM-DD HH24:MI:SS'), 2);
INSERT INTO T37 VALUES('1-1234', to_date('2011-12-6 14:30:00', 'YYYY-MM-DD HH24:MI:SS'), 1);
INSERT INTO T37 VALUES('1-2222', to_date('2011-12-6 14:32:00', 'YYYY-MM-DD HH24:MI:SS'), 1);
INSERT INTO T37 VALUES('1-2222', to_date('2011-12-6 14:35:00', 'YYYY-MM-DD HH24:MI:SS'), 4);
实测结果:
结果筛选出了 1,4,5,6,7,8。 可是第4条跟第二条间隔在5分钟以上啊.. WITH t AS (
SELECT '1-1234' t_name,TO_DATE('2011-12-6 10:57:02','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL
SELECT '1-1234' t_name,TO_DATE('2011-12-6 13:57:02','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL
SELECT '1-1234' t_name,TO_DATE('2011-12-6 13:57:04','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL
SELECT '1-1234' t_name,TO_DATE('2011-12-6 14:02:03','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL --insert
SELECT '1-1234' t_name,TO_DATE('2011-12-6 13:59:00','yyyy-mm-dd hh24:mi:ss') t_time,2 t_type FROM DUAL UNION ALL
SELECT '1-1234' t_name,TO_DATE('2011-12-6 14:30:00','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL
SELECT '1-2222' t_name,TO_DATE('2011-12-6 14:32:00','yyyy-mm-dd hh24:mi:ss') t_time,1 t_type FROM DUAL UNION ALL
SELECT '1-2222' t_name,TO_DATE('2011-12-6 14:35:00','yyyy-mm-dd hh24:mi:ss') t_time,4 t_type FROM DUAL
)
SELECT m.t_name,
TO_CHAR(m.t_time,'yyyy-mm-dd hh24:mi:ss') t_time,
m.t_type
FROM (
SELECT t.*,
COUNT(*) OVER(PARTITION BY t.t_name, t_type ORDER BY t.t_time RANGE BETWEEN CURRENT ROW AND INTERVAL '5' minute following) cn
FROM t
) m
WHERE m.cn = 1
;
不过你的忽视了一个情况
就是 name,time,type 都一样,且 time 为最大的时候。
不过这个容易解决
用row_number()就可以了。我是执行了5楼了 发现数据比你的多很多才发现有这个情况。。
5楼的数据就不对了。而且time 字段的数据都变了