题目是一个SQL查询的:一个表有一个ID字段,是主键并且是递增的,现在需要一条SQL把这个表没有的ID查出来(注意是一条SQL语句)。例如ID的记录如下:
ID
1
2
4
5
7
9
我们SQL需要把3,6,8这三个值查出来 

解决方案 »

  1.   

    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; 
      

  2.   


    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
      

  3.   


    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
      

  4.   

    --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;
      

  5.   

    --下面是上面的执行时间测试
    SQL*Plus: Release 10.2.0.1.0 - Production on 星期日 5月 15 01:26:51 2011Copyright (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 optionsSQL> 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.18SQL> --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.21SQL> --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.67SQL> --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
      

  6.   

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

  7.   

    膜拜一下大牛!

    oarcle lag()分析函数
      

  8.   

    --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行啊