SQL如何检测列含多值时通过WHERE子句动态过滤指定值

是否可以使用WHERE子句检测指定列中是否存在多个目标值，并基于检测结果过滤掉其中某一个值？
以下伪代码可以直观体现要实现的逻辑：

SELECT * 
FROM table_name
WHERE IF column includes ('value1', 'value2') THEN NOT IN 'value1'

过滤规则 #

逻辑需要自动适配上传的数据集：

• 单次上传的数据集中若仅包含value1，则保留所有value1
• 若数据集中同时存在value1和value2，则仅保留value2为有效值，过滤全部value1

示例 #

当条件成立（列中同时存在value1、value2）时，原始数据如下：

期望查询结果：

完全可以实现，只需要先对全表做一次存在性判断，再根据判断结果应用对应过滤规则即可，不需要复杂的自定义逻辑。

通用写法（兼容绝大多数SQL数据库） #

用CTE预先统计两个目标值的存在情况，再关联原表做过滤：

WITH check_result AS (
    SELECT COUNT(DISTINCT col_name) AS hit_count
    FROM table_name
    WHERE col_name IN ('value1', 'value2')
)
SELECT t.col_name
FROM table_name t, check_result cr
WHERE
    -- 两个值没有同时存在，返回所有行
    cr.hit_count < 2
    -- 两个值同时存在，排除value1
    OR (cr.hit_count = 2 AND t.col_name <> 'value1')

无CTE兼容写法 #

如果使用的数据库不支持CTE语法（比如非常老版本的MySQL），可以直接用子查询实现相同逻辑：

SELECT t.col_name
FROM table_name t
WHERE
    (SELECT COUNT(DISTINCT col_name) FROM table_name WHERE col_name IN ('value1', 'value2')) < 2
    OR t.col_name <> 'value1'

逻辑说明 #

• 子查询/CTE部分会统计目标列中value1和value2的去重数量，返回2就代表两个值同时存在，返回1或0代表没有同时出现
• 过滤条件自动适配两种场景：
  1. 两个值没有同时出现时，条件直接放行所有行，不会过滤任何数据
  2. 两个值同时存在时，仅排除值为value1的行，其余值全部保留，完全匹配需求
     如果需要扩展更多值的判断规则，只需要修改IN后面的目标值列表、以及对应过滤分支的判断条件即可，写法可以灵活调整。

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