一个小sql 看看 谁 的最高效，谢谢

[amdgaming]

题目是一个SQL查询的：一个表有一个ID字段，是主键并且是递增的，现在需要一条SQL把这个表没有的ID查出来(注意是一条SQL语句)。

例如ID的记录如下：
ID
1
2
4
5
7
9
我们SQL需要把3，6，8这三个值查出来

[秋雨飘落]

begin
for x in 1..1000000 loop
select id into mm from biao where id=x;
when mm is null
then insert into biao values(x);
end loop;

end;

[304的的哥]

with ta as(
     select 1 id from dual union all
     select 2 from dual union all
     select 3 from dual union all
     select 4 from dual union all
     select 5 from dual union all
     select 6 from dual union all
     select 7 from dual union all
     select 8 from dual union all
     select 9 from dual)
,tb as(
     select 1 id from dual union all
     select 2 from dual union all
     select 4 from dual union all
     select 5 from dual union all
     select 7 from dual union all
     select 9 from dual)
select id
from ta
where not exists(
      select id
      from tb
      where ta.id=tb.id)
/
        ID
----------
         8
         3
         6

[304的的哥]

SQL> with ta as(
  2       select level id from dual connect by level<=9
  3       )
  4  ,tb as(
  5       select 1 id from dual union all
  6       select 2 from dual union all
  7       select 4 from dual union all
  8       select 5 from dual union all
  9       select 7 from dual union all
 10       select 9 from dual)
 11  select id
 12  from ta
 13  where not exists(
 14        select id
 15        from tb
 16        where ta.id=tb.id)
 17  /

        ID
----------
         8
         3
         6

[tangren]

--1.MINUS
SELECT LEVEL id
  FROM dual
CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)
MINUS
SELECT id FROM tt;

--2.NOT EXISTS
SELECT *
  FROM (SELECT LEVEL id
          FROM dual
        CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a
 WHERE NOT EXISTS (SELECT 1 FROM tt b WHERE a.id = b.id)
 ORDER BY 1;

-3.NOT IN
SELECT *
  FROM (SELECT LEVEL id
          FROM dual
        CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a
 WHERE a.id NOT IN (SELECT b.id FROM tt b WHERE a.id = b.id);

-4.LAG
WITH t1 AS
 (SELECT s, e
    FROM (SELECT lag(id) over(ORDER BY id) + 1 s, id - 1 e FROM tt)
   WHERE e - s >= 0)
SELECT a.s + b.rn - 1 h
  FROM t1 a,
       (SELECT rownum rn
          FROM (SELECT MAX(e - s + 1) gap FROM t1)
        CONNECT BY rownum < gap) b
 WHERE a.s + b.rn - 1 <= a.e
 ORDER BY 1;

[tangren]

--下面是上面的执行时间测试
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 5月 15 01:26:51 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


连接到: 
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from tt;

        ID
----------
         1
         2
         4
         5
         7
         9
      3000
    100000

已选择8行。

SQL> set timing on;

SQL> --1.MINUS
SQL> select count(*) from (
  2  SELECT LEVEL id
  3    FROM dual
  4  CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)
  5  MINUS
  6  SELECT id FROM tt
  7  );

  COUNT(*)
----------
     99992

已用时间:  00: 00: 00.18

SQL> --2.NOT EXISTS
SQL> select count(*) from (
  2  SELECT *
  3    FROM (SELECT LEVEL id
  4            FROM dual
  5          CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a
  6   WHERE NOT EXISTS (SELECT 1 FROM tt b WHERE a.id = b.id)
  7   ORDER BY 1
  8  );

  COUNT(*)
----------
     99992

已用时间:  00: 00: 00.21

SQL> --3.NOT IN
SQL> select count(*) from (
  2  SELECT *
  3    FROM (SELECT LEVEL id
  4            FROM dual
  5          CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)) a
  6   WHERE a.id NOT IN (SELECT b.id FROM tt b WHERE a.id = b.id)
  7  );

  COUNT(*)
----------
     99992

已用时间:  00: 00: 02.67

SQL> --4.LAG 
SQL> select count(*) from (
  2  WITH t1 AS
  3   (SELECT s, e
  4      FROM (SELECT lag(id) over(ORDER BY id) + 1 s, id - 1 e FROM tt)
  5     WHERE e - s >= 0)
  6  SELECT a.s + b.rn - 1 h
  7    FROM t1 a,
  8         (SELECT rownum rn
  9            FROM (SELECT MAX(e - s + 1) gap FROM t1)
 10          CONNECT BY rownum < gap) b
 11   WHERE a.s + b.rn - 1 <= a.e
 12   ORDER BY 1
 13  );

  COUNT(*)
----------
     99991

已用时间:  00: 00: 01.96

[tangren]

更正：第四种CONNECT BY rownum < gap 应为 CONNECT BY rownum <= gap

[liuyyuns]

不错，简单的问题方法很多种。

[javatemptation]

[Quote=引用 5 楼 tangren 的回复:]

SQL code
--下面是上面的执行时间测试
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 5月 15 01:26:51 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition R……
[/Quote]



学习

[tigde]

[Quote=引用 6 楼 tangren 的回复:]

更正：第四种CONNECT BY rownum < gap 应为 CONNECT BY rownum <= gap
[/Quote]
--1.MINUS
SELECT LEVEL id
FROM dual
CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)
MINUS
SELECT id FROM tt;
用connect by level时是不是行数大于100了只会取到100行啊

[304的的哥]

[Quote=引用 4 楼 tangren 的回复:]

SQL code
--1.MINUS
SELECT LEVEL id
FROM dual
CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)
MINUS
SELECT id FROM tt;

--2.NOT EXISTS
SELECT *
FROM (SELECT LEVEL id
FROM dua……
[/Quote]
膜拜一下大牛！

oarcle lag()分析函数

[asggadfgadfg]

[Quote=引用 4 楼 tangren 的回复:]
SQL code
--1.MINUS
SELECT LEVEL id
FROM dual
CONNECT BY LEVEL <= (SELECT MAX(id) - MIN(id) + 1 FROM tt)
MINUS
SELECT id FROM tt;

--2.NOT EXISTS
SELECT *
FROM (SELECT LEVEL id
……
[/Quote]

厉害啊

[amdgaming]

厉害 呵呵

[一夜相思愁]

[Quote=引用 5 楼 tangren 的回复:]
SQL code
--下面是上面的执行时间测试
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 5月 15 01:26:51 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.


连接到:
Oracle Database 10g Enterprise Edi……
[/Quote]


如此的牛X ~~~

[dut703]

[Quote=引用 5 楼 tangren 的回复:]

SQL code
--下面是上面的执行时间测试
SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 5月 15 01:26:51 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.


连接到:
Oracle Database 10g Enterprise Edition R……
[/Quote]
膜拜。。。