有个经典的题目：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 Challenge ——快速找到1-100之间缺失的数_数据

 

 

接下来我们看看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 Challenge ——快速找到1-100之间缺失的数_数据_02

 

后面想是否递归SQL也能实现， 测试、验证自己想法的时候，发现递归SQL并不适合这样的场景。上面方法应该算是最快的方法了。当然如果你有其它更好的方法，也请多多指教。