For example:

Execute the following SQL
Select the pid from Product where pid in,2,3,4,5,6,7,8,9,10,20,55,133,155 (1);

Returns the result as follows:
Pid
-- -- -- -- --
1
3
7
10

Query the Product table of pid
But I want to know what Id pid list query condition does not meet the conditions, (2,4,5,6,8,9,20... )
Every time can only copy the query results to Excel, and then compared with the query condition list,,,

Is there any way to query returns out directly to get this result set??

CodePudding user response:

 
- all have been to the weekend 

SQL> 
SQL> As with mytable (
2 select '1' pid from dual union all 
Select 3 '3' from dual union all 
Select 4 '7' from dual union all 
Select 5 '10' from dual 
6) 
7 the select regexp_substr (,2,3,4,5,6,7,8,9,10,20,55,133,155 '1', '[^,] +' 1, level, 'I') result 
8 the from dual 
9 connect by rownum & lt; 15 - the number of + 1 
Number 10 - if you don't know can use this function 
11 - connect by rownum & lt; ,2,3,4,5,6,7,8,9,10,20,55,133,155 regexp_count (' 1 ', ', ') + 2 
12 minus 
13 the select pid from mytable; 

The RESULT 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
133 
155 
2 
20 
4 
5 
55 
6 
8 
9 

10 rows selected 
SQL> 

The RESULT 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
133 
155 
2 
20 
4 
5 
55 
6 
8 
9 

10 rows selected 

SQL> 

CodePudding user response:

Select the pid from Product where pid not in,2,3,4,5,6,7,8,9,10,20,55,133,155 (1);

CodePudding user response:

1/f method is good, but if the product table is large, it is best not to use Minus
Can be changed to the following way

With tab_pid as (
,2,3,4,5,6,7,8,9,10,20,55,133,155 select regexp_substr (' 1 ', '[^,] +' 1, level, 'I') pid
The from dual
Connect by rownum<,2,3,4,5,6,7,8,9,10,20,55,133,155=length (' 1 ') - length (the replace (,2,3,4,5,6,7,8,9,10,20,55,133,155 '1', ', ')) + 1)
Select the pid from tab_pid t
Where not the exists (select 1 from the product p where t.p id=p.p id)

CodePudding user response:

3 floor master ahhh

CodePudding user response:

- gather together a jollification

With tab_pid as (
,2,3,4,5,6,7,8,9,10,20,55,133,155 select regexp_substr (' 1 ', '[^,] +' 1, level, 'I') pid
The from dual
Connect by rownum<=length ( regexp_replace (' 1 ', '[0-9] +', ')) + 1)
Select the pid from tab_pid t
Where not the exists (select 1 from the product p where t.p id=p.p id)

CodePudding user response:

not in

CodePudding user response:

Haven't knot stick? Above also gather together a: (11. G)
With tab_pid as (
,2,3,4,5,6,7,8,9,10,20,55,133,155 select regexp_substr (' 1 ', '[^,] +' 1, level, 'I') pid
The from dual
Connect by rownum<,2,3,4,5,6,7,8,9,10,20,55,133,155=regexp_count (' 1 ', ', ') + 1
Select the pid from tab_pid t
Where not the exists (select 1 from the product p where t.p id=p.p id)