SQL Challenge ——快速找到1-100之間缺失的數

有個經典的題目：1-100之間的數字（不重覆）存放在表裡，共95行一列，但是裡面缺了5個數字，怎麼用SQL最快找出那五個數字。

我們先來看看Oracle資料庫如何實現，如下所示，我們先準備測試環境和數據。

SQL> create table t( id number(10));

Table created.

SQL> begin

  2   for i in 1 .. 100

  3   loop

  4     insert into t

  5     values(i);

  6   end loop;

  7   commit;

  8  end;

  9  /

PL/SQL procedure successfully completed.

SQL> delete from t where id in (13, 26,39,52,65);

5 rows deleted.

SQL> commit;

Commit complete.

SQL> 

那麼我們藉助dba_objects這個視圖（當然你藉助其他表或視圖都OK），生成1-100的自然數，然後和T表使用ANTI JOIN來得到查詢結果。如下所示

SQL> select rn from 

  2  ( select rownum as rn from dba_objects where rownum <=100) tt

  3   where tt.rn not in(select id from t);

        RN

----------

        65

        52

        13

        39

        26

SQL> 

接下來我們看看SQL Server資料庫如何實現，如下所示，我們先準備測試環境和數據。

CREATE TABLE T ( ID  INT);

DECLARE @Index INT =1;

WHILE @Index <= 100

BEGIN    

    INSERT INTO T VALUES(@Index);

    SET @Index +=1;

END

GO

DELETE FROM T WHERE ID IN(13, 26,39,52,65);

SELECT  RN

FROM    ( SELECT    ROW_NUMBER() OVER ( ORDER BY object_id ) AS RN

          FROM      sys.objects

        ) TT

WHERE   TT.RN <= 100

        AND TT.RN NOT IN ( SELECT   ID

                           FROM     T );

後面想是否遞歸SQL也能實現， 測試、驗證自己想法的時候，發現遞歸SQL並不適合這樣的場景。上面方法應該算是最快的方法了。當然如果你有其它更好的方法，也請多多指教。