SQL如何筛选完全不包含指定属性值的用户记录

你的判断是对的：行级WHERE color NOT IN ('red','yellow')的过滤粒度是单条记录，不是用户维度，只要用户有任意一条非red/yellow的记录就会被返回，完全不符合用户所有记录都不包含red/yellow才保留的要求。
你提出的CTE反查排除的思路逻辑是成立的，属于正确的实现方向，只是可以进一步优化，也有更简洁的通用写法。

1. 聚合判断（推荐首选，通用性最强、性能好） #

直接按user_id分组，通过HAVING判断分组内是否存在red/yellow记录，不需要嵌套多层查询，所有支持标准SQL的数据库都可以运行，绝大多数场景下执行效率最优：

SELECT user_id
FROM table_name
GROUP BY user_id
HAVING SUM(color IN ('red', 'yellow')) = 0;

逻辑说明：对每个用户的所有记录，若记录颜色为red或yellow则计1，否则计0，总和为0即代表该用户没有任何一条red/yellow的记录，完全匹配需求。
如果使用的数据库不支持布尔值隐式转数值（如SQL Server），可以用CASE表达式适配：

SELECT user_id
FROM table_name
GROUP BY user_id
HAVING SUM(CASE WHEN color IN ('red', 'yellow') THEN 1 ELSE 0 END) = 0;

2. NOT EXISTS反连接（安全、性能稳定） #

反连接是这类「排除存在某种特征的分组」需求的经典写法，相比NOT IN不会因为子查询返回NULL值出现逻辑异常，性能表现稳定：

SELECT DISTINCT user_id
FROM table_name t1
WHERE NOT EXISTS (
    SELECT 1
    FROM table_name t2
    WHERE t1.user_id = t2.user_id
      AND t2.color IN ('red', 'yellow')
);

3. 优化后的CTE + NOT IN写法 #

你原本的思路可以正常使用，只需要补全CTE逻辑，同时注意给子查询去重、规避NULL值问题即可：

WITH invalid_users AS (
    SELECT DISTINCT user_id
    FROM table_name
    WHERE color IN ('red', 'yellow')
)
SELECT DISTINCT user_id
FROM table_name
WHERE user_id NOT IN (SELECT user_id FROM invalid_users);

注意：如果CTE返回的user_id存在NULL值，NOT IN会导致整个查询返回空结果，使用时需要确认user_id字段为非空约束，或者在子查询里加WHERE user_id IS NOT NULL过滤。

• 日常开发优先选聚合+HAVING的写法，代码最简洁，语义直观，后续维护成本最低。
• 数据量极大、且user_id字段建有索引时，NOT EXISTS写法的性能和聚合写法基本持平，可以根据团队的SQL编写习惯选择。
• 不推荐使用未做空值处理的NOT IN写法，容易出现隐蔽的线上bug。

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