如何在单次SQL查询中判断OR条件里哪些匹配成功？

安全阿岳

2026-8-3

修正后的SQL写法及问题说明

原SQL存在几个明显问题：

字段拼写错误：多处将field写成了flield
CASE语句逻辑错误：原写法用顺序WHEN判断，只会返回第一个满足的条件，无法同时匹配多个OR条件；而且THEN子句里的赋值写法不符合SQL语法
WHERE子句的条件值和CASE里的不统一（比如CASE里判断field2='yyy'，WHERE里却是field2='yyyy'）

要实现一次查询找出所有匹配的OR条件，正确的写法应该是对每个条件单独判断，再把满足的描述拼接起来：

SELECT 'MATCHED' AS ``,
       CONCAT(
           CASE WHEN field1 = 'xxx' THEN 'field1 matched ' ELSE '' END,
           CASE WHEN field2 = 'yyyy' THEN 'field2 matched ' ELSE '' END,
           CASE WHEN field3 = 'zzzz' THEN 'field3 matched ' ELSE '' END
       ) AS result
FROM `table`
WHERE field1 = 'xxx' OR field2 = 'yyyy' OR field3 = 'zzzz';

如果不想让结果末尾有多余空格，可以用TRIM()包裹，或者用CONCAT_WS（自动用分隔符拼接，忽略空值）优化：

SELECT 'MATCHED' AS ``,
       TRIM(CONCAT_WS(' ',
           CASE WHEN field1 = 'xxx' THEN 'field1 matched' END,
           CASE WHEN field2 = 'yyyy' THEN 'field2 matched' END,
           CASE WHEN field3 = 'zzzz' THEN 'field3 matched' END
       )) AS result
FROM `table`
WHERE field1 = 'xxx' OR field2 = 'yyyy' OR field3 = 'zzzz';