oracle查询缺失数据

[刘痕]

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 SS NOT IN (SELECT d FROM tab)

各位大神，这样数据大了好慢。有没有能快点的。

[流浪川]

引用 楼主 hateson 的回复:

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 SS NOT IN (SELECT d FROM tab) 各位大神，这样数据大了好慢。有没有能快点的。

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

[刘痕]

引用 1 楼 hidanger521 的回复:

[quote=引用 楼主 hateson 的回复:] 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 SS NOT IN (SELECT d FROM tab) 各位大神，这样数据大了好慢。有没有能快点的。

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[/quote] 这个也很慢，这个数据最后还要分组查询了等，还是不行。。。。我看网上例子只有查询间断1条数据的，没有多条的。

[流浪川]

分组查询，你是不是应该先分组再利用这个查询进行左关联来实现呢？

[刘痕]

引用 3 楼 hidanger521 的回复:

分组查询，你是不是应该先分组再利用这个查询进行左关联来实现呢？

是的，在里面语句分组了然后按再查询的，所以很慢。感觉视图好像无法实现了。我得重新搞个过程算了。。。。