如何提取Table_A中含Table_B未收录CODE的记录？

从Table_A中提取满足以下条件的记录生成Table_C：

• Table_A的Attr列中包含CODE值
• 这些CODE值里至少有一个未被Table_B的CODE列收录

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

Table_A #

Table_B #

当前SQL逻辑错误：NOT EXISTS的条件是Table_B中没有任何一个CODE出现在当前Table_A的Attr里，但需求是Attr里存在至少一个CODE不在Table_B中，两者逻辑完全相反。

要实现需求，需先从Attr中提取所有CODE值，再判断是否存在未在Table_B中出现的CODE，以下是针对不同数据库的实现方案：

方案1：适用于PostgreSQL #

SELECT DISTINCT a.*
FROM Table_A a
WHERE a.Attr LIKE '%CODE%'
AND EXISTS (
    SELECT 1
    FROM unnest(
        string_to_array(
            regexp_replace(
                substring(a.Attr FROM 'CODE = (.*)'),
                '[''\s]', '', 'g'
            ),
            ','
        ) AS code_val
    )
    WHERE code_val NOT IN (SELECT CODE FROM Table_B)
);

方案2：适用于MySQL 8.0+ #

SELECT DISTINCT a.*
FROM Table_A a
WHERE a.Attr LIKE '%CODE%'
AND EXISTS (
    SELECT 1
    FROM (
        SELECT TRIM(
            BOTH ''' ' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(
                regexp_replace(a.Attr, '.*CODE = (.*)', '\\1'),
                ',', n.n
            ), ',', -1)
        ) AS code_val
        FROM (
            SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
        ) n
        WHERE n.n <= LENGTH(regexp_replace(a.Attr, '.*CODE = (.*)', '\\1')) - LENGTH(REPLACE(regexp_replace(a.Attr, '.*CODE = (.*)', '\\1'), ',', '')) + 1
    ) AS split_codes
    WHERE split_codes.code_val NOT IN (SELECT CODE FROM Table_B)
);

方案3：通用应急思路（无字符串拆分函数时） #

依赖CODE以逗号分隔的规则，通过计数判断是否存在未收录的CODE：

SELECT *
FROM Table_A a
WHERE a.Attr LIKE '%CODE%'
AND NOT (
    SELECT COUNT(*)
    FROM Table_B b
    WHERE a.Attr LIKE '%' || b.CODE || '%'
) = (
    LENGTH(a.Attr) - LENGTH(REPLACE(a.Attr, ',', '')) + 1
);

注：此方案精度有限，若Attr中存在非CODE的逗号会影响结果。

内容的提问来源于stack exchange，提问作者NEWBIE