3,491
社区成员
发帖
与我相关
我的任务
分享
select * from(
select regexp_substr('C005-201609080344,C001-201609080343,C001-201609060317','[^,]+',1,rownum) bill_no from dual
connect by rownum<=length('C005-201609080344,C001-201609080343,C001-201609060317')-length(replace('C005-201609080344,C001-201609080343,C001-201609060317',','))+1
) t
where not exists (select 1 from B t2 where t2.bill_no = t.bill_no)
这样也行,但是当字符串很长的时候看着就比较闹心了
-- 两个方法,各有优势,你自己权衡一下
-- 1 创建一个 TYPE ,直接 使用 from 方法, 但是,不能超过 999 个值 。
SQL> create type type_list is table of varchar2(60) ;
2 /
Type created
SQL> select * from table(type_list('C005-201609080344','C001-201609080343','C001-201609060317')) t ;
COLUMN_VALUE
------------------------------------------------------------
C005-201609080344
C001-201609080343
C001-201609060317
SQL> drop type type_list ;
Type dropped
-- 2 在你的程序中,拼接成 虚拟表,这个数量没有限制
SQL> with m(orderid) as (
2 select 'C005-201609080344' from dual union all
3 select 'C001-201609080343' from dual union all
4 select 'C001-201609060317' from dual
5 )
6 select * from m ;
ORDERID
-----------------
C005-201609080344
C001-201609080343
C001-201609060317
SQL>