表a
nameid text
A ABCD
A AVDS
A DSDS
A DSDSA
.
.
.
表b
nameid text
A AB*D
A DS*
.
.
. 表b 中*表示通配符,*可以代表多个字母和数字
现在是需要一句SQL,结果是表a中text不满足表b中text的所有数据结果应该是
nameid text
A AVDS
nameid text
A ABCD
A AVDS
A DSDS
A DSDSA
.
.
.
表b
nameid text
A AB*D
A DS*
.
.
. 表b 中*表示通配符,*可以代表多个字母和数字
现在是需要一句SQL,结果是表a中text不满足表b中text的所有数据结果应该是
nameid text
A AVDS
with table_a as
(select 'A' as nameid, 'ABCD' as text
from dual
union all
select 'A', 'AVDS'
from dual
union all
select 'A', 'DSDS'
from dual
union all
select 'A', 'DSDSA' from dual),
table_b as
(select 'A' as nameid, 'AB*D' as text
from dual
union all
select 'A', 'DS*' from dual)
select a.*
from table_a a
where a.text not in
(select a1.text
from table_a a1, table_b b1
where a1.nameid = b1.nameid
and a1.text like replace(b1.text, '*', '%'))