表A
id name time
1 aa 2009-01-01 00:10
2 aa 2009-01-01 00:20
5 aa 2009-01-01 00:30
10 aa 2009-01-01 00:50
11 aa 2009-01-01 01:20
12 aa 2009-01-01 01:30id不连续,time格式固定每十分接收一次,不会重复只会缺少,我要如何得到下面这些数据?
2009-01-01 00:40
2009-01-01 01:00
2009-01-01 01:10
数据库为oracle,数据量比较大(1000多w),不想通过程序来实现。
id name time
1 aa 2009-01-01 00:10
2 aa 2009-01-01 00:20
5 aa 2009-01-01 00:30
10 aa 2009-01-01 00:50
11 aa 2009-01-01 01:20
12 aa 2009-01-01 01:30id不连续,time格式固定每十分接收一次,不会重复只会缺少,我要如何得到下面这些数据?
2009-01-01 00:40
2009-01-01 01:00
2009-01-01 01:10
数据库为oracle,数据量比较大(1000多w),不想通过程序来实现。
然后再用这张time表跟你的A表拼接,查出缺的记录就可以了。
begin
insert into test values(1, 'aa', to_date('2009-01-01 00:10', 'yyyy-mm-dd hh24:mi'));
insert into test values(2 , 'aa', to_date('2009-01-01 00:20', 'yyyy-mm-dd hh24:mi'));
insert into test values(5 , 'aa', to_date('2009-01-01 00:30', 'yyyy-mm-dd hh24:mi'));
insert into test values(10, 'aa', to_date('2009-01-01 00:50', 'yyyy-mm-dd hh24:mi'));
insert into test values(11, 'aa', to_date('2009-01-01 01:20', 'yyyy-mm-dd hh24:mi'));
insert into test values(12, 'aa', to_date('2009-01-01 01:30 ', 'yyyy-mm-dd hh24:mi'));
commit;
end;
select opt_time
from (select to_date('2009-01-01', 'yyyy-mm-dd') + level * 10 / 60 / 24 opt_time
from dual
connect by level <= 12 * 6
union
select to_date('2009-01-01 12:00', 'yyyy-mm-dd hh24:mi') +
level * 10 / 60 / 24 opt_time
from dual
connect by level < 12 * 6)
where opt_time not in (select opt_time from test);
ID NAME TIMES
---------- ---- --------------------
1 AA 2009-1-1 0:10:00
2 AA 2009-1-1 0:20:00
5 AA 2009-1-1 0:30:00
10 AA 2009-1-1 0:50:00
11 AA 2009-1-1 1:20:00
12 AA 2009-1-1 1:30:00
6 rows selectedSQL> SELECT RN_TIME
2 FROM (SELECT MIN_TIMES + (ROWNUM - 1) * 10 / 60 / 24 RN_TIME
3 FROM DUAL, (SELECT MIN(TIMES) MIN_TIMES FROM TEST_DATE)
4 CONNECT BY ROWNUM <= (SELECT (MAX(TIMES) - MIN(TIMES)) * 24 * 60 / 10
5 FROM TEST_DATE) + 1) T1
6 WHERE NOT EXISTS (SELECT 1 FROM TEST_DATE T2 WHERE T2.TIMES = T1.RN_TIME);
RN_TIME
--------------------
2009-1-1 0:40:00
2009-1-1 1:10:00
2009-1-1 1:00:00
谢谢!