如何关联多表获取满足GROUP BY/HAVING及特定匹配条件的结果集

我有两张表：Input_table 和 Xref_table，表结构及数据如下：

Input_table #

Xref_table #

关联规则 #

需要按以下条件关联两张表：

• Input表的column_nm需匹配keywords_primary与keywords_secondary的组合模式，两者前后或中间带有分隔符（例如ssn_no，其中ssn为primary，no为secondary）；
• Input表的column_nm需匹配keywords_primary，且将column_nm中的keywords_primary替换为空后，不含其他字母。

当前问题 #

我编写的SQL如下：

SELECT input.ait_no,
       input.schema_nm,
       input.table_nm,
       input.column_nm,
       xref.entity_category
FROM   input_table input
       INNER JOIN xref_table xref
               ON ( ( ( input.column_nm LIKE
                        '%[^a-zA-Z]' + xref.keywords_primary
                        + '[^a-zA-Z]%' )
                       OR ( input.column_nm LIKE xref.keywords_primary +
                                                 '[^a-zA-Z]%' )
                    )
                    AND ( ( input.column_nm LIKE
                            '%[^a-zA-Z]' + xref.keywords_secondary
                            + '[^a-zA-Z]%' )
                           OR ( input.column_nm LIKE xref.keywords_secondary +
                                                     '[^a-zA-Z]%' )
                           OR ( input.column_nm LIKE
                                '%[^a-zA-Z]' + xref.keywords_secondary ) ) )
                   OR ( REPLACE(input.column_nm, xref.keywords_primary, '') NOT
                        LIKE
                        '%[a-zA-Z]%' )
GROUP  BY input.ait_no,
          input.schema_nm,
          input.table_nm,
          input.column_nm,
          xref.entity_category
HAVING NOT Max(xref.excld_sw) = 1 

现在的问题是：满足REPLACE(input.column_nm,xref.keywords_primary,'') NOT LIKE '%[a-zA-Z]%'条件的行，因为关联到excld_sw=1的记录，被GROUP BY和HAVING过滤掉了（比如ssn_1符合匹配条件但未被返回）。

期望结果 #

需要同时获取以下两类行：

1. 满足原GROUP BY和HAVING子句的行；
2. 满足上述REPLACE匹配条件的行（即使不满足原GROUP BY和HAVING）。

期望输出的Output_table如下：

把两种匹配逻辑拆成两个独立查询，用UNION ALL合并后去重，就能避免两类条件互相干扰：

-- 1. 匹配primary+secondary组合且未被排除的行
SELECT input.ait_no,
       input.schema_nm,
       input.table_nm,
       input.column_nm,
       xref.entity_category
FROM input_table input
JOIN xref_table xref
    ON ( (input.column_nm LIKE '%[^a-zA-Z]' + xref.keywords_primary + '[^a-zA-Z]%'
          OR input.column_nm LIKE xref.keywords_primary + '[^a-zA-Z]%')
         AND (input.column_nm LIKE '%[^a-zA-Z]' + xref.keywords_secondary + '[^a-zA-Z]%'
              OR input.column_nm LIKE xref.keywords_secondary + '[^a-zA-Z]%'
              OR input.column_nm LIKE '%[^a-zA-Z]' + xref.keywords_secondary) )
WHERE xref.excld_sw = 0

UNION ALL

-- 2. 满足REPLACE条件的行（排除已在第一个查询中返回的行）
SELECT input.ait_no,
       input.schema_nm,
       input.table_nm,
       input.column_nm,
       xref.entity_category
FROM input_table input
JOIN xref_table xref
    ON input.column_nm LIKE '%' + xref.keywords_primary + '%'
       AND REPLACE(input.column_nm, xref.keywords_primary, '') NOT LIKE '%[a-zA-Z]%'
WHERE NOT EXISTS (
    SELECT 1
    FROM xref_table x
    WHERE x.keywords_primary = xref.keywords_primary
      AND x.keywords_secondary = xref.keywords_secondary
      AND ( (input.column_nm LIKE '%[^a-zA-Z]' + x.keywords_primary + '[^a-zA-Z]%'
             OR input.column_nm LIKE x.keywords_primary + '[^a-zA-Z]%')
            AND (input.column_nm LIKE '%[^a-zA-Z]' + x.keywords_secondary + '[^a-zA-Z]%'
                 OR input.column_nm LIKE x.keywords_secondary + '[^a-zA-Z]%'
                 OR input.column_nm LIKE '%[^a-zA-Z]' + x.keywords_secondary) )
)

-- 去重并整理结果
GROUP BY ait_no, schema_nm, table_nm, column_nm, entity_category
ORDER BY ait_no, column_nm;

逻辑说明 #

• 第一个查询直接筛选符合组合模式且未被排除的记录，无需分组过滤；
• 第二个查询单独处理REPLACE规则，同时用NOT EXISTS排除已经在第一个查询中出现的行，避免重复；
• 最后通过GROUP BY去重，保证结果与预期一致。

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