-- 输入最大时间MAX_DATE, 最小时间MIN_DATE.然后运行下面的SQL,会出来MAX_DATE到MIN_DATE间的所有的天数.然后再匹配你输入数据的时间找出没有的就可以了. SELECT &MIN_DATE + ROWNUM - 1 RN FROM DUAL CONNECT BY ROWNUM <= (SELECT &MAX_DATE - &MIN_DATE FROM DUAL);
查出某段时间内连续日期,用CONNECT BY ROWNUM,如楼上.然后使用not exists比对业务表中的时间字段,即可将没有数据的日期查出.
我写个完整的,仅供参考,^_^ 9i以上版本适用with tab_a as ( select max(trunc(date_time)) max_time,min(trunc(date_time)) min_time from your_table_name ) select a.dt from ( select (select max_time from tab_a)+rownum-1 dt from dual connect by rownum < (select max_time-min_time from tab_a) ) a,your_table_name b where a.dt=b.date_time(+) and b.date_time is null
SELECT &MIN_DATE + ROWNUM - 1 RN
FROM DUAL
CONNECT BY ROWNUM <= (SELECT &MAX_DATE - &MIN_DATE
FROM DUAL);
9i以上版本适用with tab_a as (
select max(trunc(date_time)) max_time,min(trunc(date_time)) min_time
from your_table_name
)
select a.dt
from (
select (select max_time from tab_a)+rownum-1 dt
from dual
connect by rownum < (select max_time-min_time from tab_a)
) a,your_table_name b
where a.dt=b.date_time(+)
and b.date_time is null