SQL查询错误排查：实现name与match表type关联匹配

表结构与数据 #

name表 #

ID| type  |
--| ----  |
 1| 1ABC  |
 2| 2DEF  |
 3| 3DEF  |
 4| 4IJK  |

match表 #

需求与原错误SQL #

需要实现name表的type字段与match表的type规则匹配，返回对应DATA值，但原SQL出现缺失关键字错误：

SELECT s.id, s.type, m.data
where case when substr(m.type1,3)='NOT' then s.type not in (REPLACE(REPLACE(m.type,'NOT',''),'AND',','))
          ELSE s.type in (m.type) end
from source s, match m;

期望输出 #

ID|type|DATA
1 |1ABC|NOT DEF AND NOT IJK
2 |2DEF|DEF ONLY
3 |3DEF|DEF ONLY
4 |4IJK|NOT ABC AND NOT DEF

问题分析与修正SQL #

原SQL的问题 #

1. 语法顺序错误：FROM子句必须放在WHERE之前，原SQL顺序颠倒导致语法报错。
2. 匹配方式错误：用IN处理模糊匹配规则完全不合适，这类场景应该用LIKE来匹配通配符条件。
3. 字段笔误：m.type1是不存在的字段，应为m.type。
4. 逻辑错误：通过REPLACE转换条件的方式无法正确解析模糊匹配规则，逻辑不成立。

修正后的SQL（关联match表实现动态匹配） #

SELECT 
    s.id,
    s.type,
    m.data
FROM name s
INNER JOIN match m ON
    CASE
        -- 处理"NOT X AND NOT Y"格式的规则
        WHEN m.type LIKE 'NOT % AND NOT %' THEN
            s.type NOT LIKE SUBSTRING(m.type, 5, INSTR(m.type, ' AND NOT ') - 5)
            AND s.type NOT LIKE SUBSTRING(m.type, INSTR(m.type, ' AND NOT ') + 9)
        -- 处理单一通配符规则
        ELSE
            s.type LIKE m.type
    END;

硬编码规则的简化SQL（如果match表规则固定） #

如果match表的规则不会频繁变动，也可以直接用CASE语句硬编码逻辑，性能更优：

SELECT 
    id,
    type,
    CASE
        WHEN type LIKE '%DEF%' THEN 'DEF ONLY'
        WHEN type NOT LIKE '%DEF%' AND type NOT LIKE '%IJK%' THEN 'NOT DEF AND NOT IJK'
        WHEN type NOT LIKE '%ABC%' AND type NOT LIKE '%DEF%' THEN 'NOT ABC AND NOT DEF'
    END AS DATA
FROM name;

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