原文:SQL Challenge ——快速找到1-100之间缺失的数
有个经典的题目:1-100之间的数字(不重复)存放在表里,共95行一列,但是里面缺了5个数字,怎么用SQL最快找出那五个数字。
我们先来看看Oracle数据库如何实现,如下所示,我们先准备测试环境和数据。
SQL>createtable t( id number(10));
Table created.
SQL>begin
2for iin 1 .. 100
3 loop
4 insertinto t
5values(i);
6end loop;
7commit;
8end;
9 /
PL/SQLprocedure successfully completed.
SQL>deletefrom twhere idin (13, 26,39,52,65);
5rows deleted.
SQL>commit;
Commit complete.
SQL>
那么我们借助dba_objects这个视图(当然你借助其他表或视图都OK),生成1-100的自然数,然后和T表使用ANTI JOIN来得到查询结果。如下所示
SQL>select rnfrom
2 (select rownumas rnfrom dba_objectswhere rownum <=100) tt
3where tt.rnnotin(select idfrom t);
RN
----------
65
52
13
39
26
SQL>
接下来我们看看SQL Server数据库如何实现,如下所示,我们先准备测试环境和数据。
CREATETABLE T ( IDINT);
DECLARE @IndexINT =1;
WHILE @Index <= 100
BEGIN
INSERTINTO TVALUES(@Index);
SET @Index +=1;
END
GO
DELETEFROM TWHERE IDIN(13, 26,39,52,65);
SELECT RN
FROM (SELECT ROW_NUMBER()OVER (ORDERBY object_id )AS RN
FROM sys.objects
) TT
WHERE TT.RN <= 100
AND TT.RNNOTIN (SELECT ID
FROM T );
后面想是否递归SQL也能实现, 测试、验证自己想法的时候,发现递归SQL并不适合这样的场景。上面方法应该算是最快的方法了。当然如果你有其它更好的方法,也请多多指教。