SQL challenge--quickly find the missing number between 1-100

There is a classic title: 1-100 of the number (not repeating) stored in the table, a total of 95 rows of a column, but there is missing 5 numbers, how to use SQL to find the fastest five numbers.

Let's take a look at how the Oracle database is implemented, as shown below, and we'll prepare the test environment and data first.

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.

So we use the Dba_objects view (of course you have all the other tables or views OK), generate 1-100 of the natural number, and then the T table with the anti join to get the query results. As shown below

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

Let's look at how the SQL Server database is implemented, as shown below, before we prepare the test environment and data.

CREATE TABLE T (ID INT);

DECLARE @Index INT = 1;

While @Index <= 100

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

It is not appropriate to find that recursive SQL is not suitable for this scenario when you want to test and validate your own ideas. The method above should be considered as the quickest method. Of course, if you have other better ways, please advise.

Xiaoxiang Hidden Source: http://www.cnblogs.com/kerrycode/
If you really feel that the article is well written and helpful to you, then you might as well give a little reward, if the bag is shy, may wish to help "recommend", your "recommendation" and "reward" will be my greatest writing power!
This article is copyrighted by the author, welcome reprint, but without the consent of the author must retain this paragraph, and in the article page obvious location to give the original text connection. Good text to the top of my collection this article Xiaoxiang hermit
Follow-149
Fans-1914 honors: Recommended blog + add attention to 00? Previous post: Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot B E impersonated, or do not have permission.
? Next: VMware platform Windows Server 2012 No response downtime

SQL challenge--quickly find the missing number between 1-100