with tab as (
select '2001' d from dual union all
select '2002' d from dual union all
select '2003' d from dual union all
select '2006' d from dual union all
select '2007' d from dual union all
select '2008' d from dual)
SELECT SS
FROM (SELECT SN + (level - 1) SS
FROM (select min(d) SN,max(d) EN from tab) T
CONNECT BY SN + (level - 1) <= EN)
WHERE NOT EXISTS(SELECT d FROM tab k WHERE k.d=SS)
会不会好点,不要用not in
select '2001' d from dual union all
select '2002' d from dual union all
select '2003' d from dual union all
select '2006' d from dual union all
select '2007' d from dual union all
select '2008' d from dual)
SELECT SS
FROM (SELECT SN + (level - 1) SS
FROM (select min(d) SN,max(d) EN from tab) T
CONNECT BY SN + (level - 1) <= EN)
WHERE NOT EXISTS(SELECT d FROM tab k WHERE k.d=SS)
会不会好点,不要用not in
select '2001' d from dual union all
select '2002' d from dual union all
select '2003' d from dual union all
select '2006' d from dual union all
select '2007' d from dual union all
select '2008' d from dual)
SELECT SS
FROM (SELECT SN + (level - 1) SS
FROM (select min(d) SN,max(d) EN from tab) T
CONNECT BY SN + (level - 1) <= EN)
WHERE NOT EXISTS(SELECT d FROM tab k WHERE k.d=SS)
会不会好点,不要用not in这个也很慢,这个数据最后还要分组查询了等,还是不行。我看网上例子只有查询间断1条数据的,没有多条的。