Like 运算符来比较两个不同表中的两列

您必须提供别名，以便可以在嵌套选择中引用它：Table_A

SELECT *
FROM
    Table_A A
WHERE
    Attr LIKE '%CODE%' AND
    NOT EXISTS
    (
        SELECT *
        FROM Table_B
        WHERE A.Attr LIKE '%'||Table_B.CODE||'%'
    )

此外，关键字不是 .EXISTSEXIST

SQL 无法解析复杂的任意表达式，例如 and 区分代码和项目。但是，如果项目 ID 始终与代码不同，则这不是问题。ITEM = 'AFRD' AND CODE = 'C333'C333AFRD

请参见：http://sqlfiddle.com/#!4/995f23/1/0

只要代码始终跟在项目后面，就可以使用以下命令提取代码列表：

regexp_substr(attr, '.*(CODE = ?)(.*?)', 1, 1, null, 2

并删除空格和引号

translate(regexp_substr(attr, '.*(CODE = ?)(.*?)', 1, 1, null, 2), q'^x' ^', 'x')

获取要查找的简单逗号分隔值列表，例如 ;然后将其拆分为单独的元素，例如使用递归子查询分解;并找到其他表中不存在的任何内容。G777,B222

这很混乱，但是：

with cte (id, attr, codes) as (
  select id, attr,
    translate(
      regexp_substr(attr, '.*(CODE = ?)(.*?)', 1, 1, null, 2),
      q'^x' ^', 'x'
    )
  from table_a
),
rcte (id, attr, codes, pos, code) as (
  select id, attr, codes, 1, regexp_substr(codes, '(.*?)(,|$)', 1, 1, null, 1)
  from cte
  where codes is not null
  union all
  select id, attr, codes, pos + 1, regexp_substr(codes, '(.*?)(,|$)', 1, pos + 1, null, 1)
  from rcte
  where regexp_substr(codes, '(.*?)(,|$)', 1, pos + 1, null, 1) is not null
)
select distinct r.id, r.attr
from rcte r
where not exists (
  select null
  from table_b b
  where b.code = r.code
)

给

db<>fiddle

The gets the simple CSV values; splits those up to individual components (and assumes there will be no empty elements), and then those individual values are checked with . Which gives duplicate id/attr pairs, so removes the duplicates; but you can also see which code(s) didn't exist if you want, by not applying distinct, and potentially using to still get a single result per ID.cterctenot existsdistinctlistagg

It's painful, but that's what happens with data stored like this...

You can use an outer join among Table_A and Table_B in order to obtain unmatched codes after converting comma separated codes to the newly generated rows per each individual code through use of regular expression functions along with a hierarchical query such as

WITH a0 AS
(
 SELECT id, CASE WHEN INSTR(attr, 'CODE')>0 THEN
                      REGEXP_REPLACE(attr,'(.*CODE =)+') 
                  END AS codes
   FROM Table_A
), a AS
(   
 SELECT id, TRIM(BOTH CHR(39) FROM TRIM(REGEXP_SUBSTR(codes,'[^,]+',1,level)) ) AS codes
   FROM a0
  WHERE codes IS NOT NULL 
CONNECT BY LEVEL <= REGEXP_COUNT(codes, ',') + 1
    AND PRIOR SYS_GUID() IS NOT NULL
    AND PRIOR id = id   
)
SELECT a.*
  FROM Table_A a
  JOIN (SELECT DISTINCT id 
          FROM a 
          LEFT JOIN Table_B 
            ON code = codes 
         WHERE code IS NULL) b
     ON a.id = b.id 

Demo