To find the serial number of the missing number-CodePudding

Build table below
Create table test (id number (4));
Insert into test (id) values (1);
Insert into test (id) values (2);
Insert into test (id) values (4);
Insert into test (id) values (5);
Insert into test (id) values (6);
Insert into test (id) values (8);
Insert into test (id) values (9);

How to query the missing number?
Serial number should be 1 to 9, but missing 3 and 7
The query results should be 3, 7

CodePudding user response:

The number, or the detail?

CodePudding user response:

reference 1st floor wmxcn2000 response:

results to number, or the detail?

As long as the number, find out 3, 7 can
Thank you for your help

CodePudding user response:

 with t1 as 
(
Select 1 id from dual union all 
Select 2 id from dual union all 
Select 4 id from dual union all 
Select 5 id from dual union all 
Select 6 id from dual union all 
The select 8 id from dual union all 
The select 9 id from dual 
) 
, t2 as 
(
The select level id2 from dual connect by level<=(select Max (id) from t1) 
) 
Select * from t2 where id2 not in (select id from t1)

CodePudding user response:

 
The select b.r n from 
Test a, 
(select rownum rn from dual connect by rownum & lt; B=10) 
Where b.r n=Anderson d (+) 
And Anderson, d is null

CodePudding user response:

 
- gather together a number of 
With t1 as 
(
Select 1 id from dual union all 
Select 2 id from dual union all 
Select 4 id from dual union all 
Select 5 id from dual union all 
Select 6 id from dual union all 
The select 8 id from dual union all 
The select 9 id from dual 
) 
Select rownum from dual connect by rownum & lt;=10 
Minus 
Select the id from t1