例如：
執行如下SQL
select pid from Product where pid in (1,2,3,4,5,6,7,8,9,10,20,55,133,155);
回傳結果如下：
pid
-————
1
3
7
10
查詢得到了Product表中存在的pid
但我想知道查詢條件的pid 串列中哪些Id不滿足條件，（2,4,5,6,8,9,20.....）
每次只能拷貝查詢結果到Excel，然后與查詢條件串列對比。。。
有沒有什么辦法直接查詢回傳出來得到這個結果集？？
uj5u.com熱心網友回復：
-- 都去過周末了
SQL>
SQL> with mytable as (
2 select '1' pid from dual union all
3 select '3' from dual union all
4 select '7' from dual union all
5 select '10' from dual
6 )
7 select regexp_substr('1,2,3,4,5,6,7,8,9,10,20,55,133,155','[^,]+',1,level,'i') result
8 from dual
9 connect by rownum < 15 -- 個數 + 1
10 -- 如果不知道個數可以用這個 函式
11 -- connect by rownum < regexp_count('1,2,3,4,5,6,7,8,9,10,20,55,133,155',',')+2
12 minus
13 select pid from mytable ;
RESULT
--------------------------------------------------------------------------------
133
155
2
20
4
5
55
6
8
9
10 rows selected
SQL> /
RESULT
--------------------------------------------------------------------------------
133
155
2
20
4
5
55
6
8
9
10 rows selected
SQL>
uj5u.com熱心網友回復：
select pid from Product where pid not in (1,2,3,4,5,6,7,8,9,10,20,55,133,155);uj5u.com熱心網友回復：
1樓方法不錯。但是如果product表比較大，最好不要用Minus可以改成以下方式
with tab_pid as(
select regexp_substr('1,2,3,4,5,6,7,8,9,10,20,55,133,155','[^,]+',1,level,'i') pid
from dual
connect by rownum<=length('1,2,3,4,5,6,7,8,9,10,20,55,133,155')-length(replace('1,2,3,4,5,6,7,8,9,10,20,55,133,155',','))+1)
select pid from tab_pid t
where not exists(select 1 from product p where t.pid=p.pid)
uj5u.com熱心網友回復：
3樓高人啊啊啊uj5u.com熱心網友回復：
--湊個熱鬧with tab_pid as(
select regexp_substr('1,2,3,4,5,6,7,8,9,10,20,55,133,155','[^,]+',1,level,'i') pid
from dual
connect by rownum<=length(regexp_replace('1,2,3,4,5,6,7,8,9,10,20,55,133,155','[0-9]+',''))+1)
select pid from tab_pid t
where not exists(select 1 from product p where t.pid=p.pid)
uj5u.com熱心網友回復：
not inuj5u.com熱心網友回復：
還沒結貼？也湊一個：(11.g以上)with tab_pid as(
select regexp_substr('1,2,3,4,5,6,7,8,9,10,20,55,133,155','[^,]+',1,level,'i') pid
from dual
connect by rownum<=regexp_count('1,2,3,4,5,6,7,8,9,10,20,55,133,155',',')+1
select pid from tab_pid t
where not exists(select 1 from product p where t.pid=p.pid)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/113576.html
標籤：基礎和管理
上一篇：mysql 臨時表插入資料報錯