如何简化筛选满足特定食物偏好用户全量行的SQL查询逻辑？

示例表结构与数据 #

CREATE TABLE t (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    food VARCHAR(50)
);
INSERT INTO t (name, food) VALUES
('john', 'pizza'),
('john', 'cake'),
('andrew', 'pizza'),
('andrew', 'pizza'),
('andrew', 'pizza'),
('matt', 'pizza'),
('matt', 'pizza'),
('matt', 'burger'),
('david', 'cake'),
('david', 'pizza'),
('david', 'pizza'),
('elen', 'cake'),
('elen', 'pizza'),
('elen', 'donuts'),
('claire', 'cake'),
('claire', 'donuts'),
('claire', 'tacos'),
('john', 'pizza'),
('john', 'cake'),
('matt', 'apples'),
('matt', 'tacos');

以下针对你提出的4种场景，给出简化后的SQL逻辑及思路：

场景1：筛选仅喜欢pizza的用户的所有行 #

需求：仅保留所有食物记录都是pizza的用户的全量行（如andrew的所有行，john因有cake记录不符合）

简化SQL（PostgreSQL专属）

SELECT *
FROM t
WHERE name IN (
    SELECT name
    FROM t
    GROUP BY name
    HAVING EVERY(food = 'pizza')
);

通用兼容版本（适配MySQL、SQL Server等）

SELECT *
FROM t
WHERE name IN (
    SELECT name
    FROM t
    GROUP BY name
    HAVING COUNT(DISTINCT food) = 1 AND MAX(food) = 'pizza'
);

简化思路：

• 去掉原SQL中子查询的WHERE food IN ('pizza')，避免丢失用户的非pizza记录导致误判
• 用EVERY()函数直接表达「分组内所有行都满足条件」的逻辑，比CASE表达式更直观；通用版本通过COUNT(DISTINCT food)=1确保仅有一种食物，再验证该食物是pizza

场景2：筛选仅喜欢pizza和cake的用户的所有行 #

需求：用户的所有食物只能是pizza或cake，不能有其他食物（如john、david的行符合，elen因有donuts记录不符合）

简化SQL（PostgreSQL专属）

SELECT *
FROM t
WHERE name IN (
    SELECT name
    FROM t
    GROUP BY name
    HAVING EVERY(food IN ('pizza', 'cake'))
);

通用兼容版本

SELECT *
FROM t
WHERE name IN (
    SELECT name
    FROM t
    GROUP BY name
    HAVING MIN(CASE WHEN food NOT IN ('pizza', 'cake') THEN 0 ELSE 1 END) = 1
);

简化思路：

• 原SQL子查询的WHERE food IN ('pizza', 'cake')会过滤掉用户的其他食物记录，导致误判（如elen的donuts被过滤后会被错误判定为符合条件），必须移除该过滤条件
• 用EVERY()直接判断「所有食物都在指定范围内」，通用版本通过CASE表达式标记非目标食物，再用MIN确保没有不符合的记录

场景3：筛选完全不喜欢pizza的用户的所有行 #

需求：用户的所有食物记录中完全没有pizza（如claire的所有行）

简化SQL

SELECT *
FROM t
WHERE NOT EXISTS (
    SELECT 1
    FROM t t2
    WHERE t2.name = t.name AND t2.food = 'pizza'
);

简化思路：

• 用NOT EXISTS替代原有的NOT IN，避免NOT IN遇到子查询返回NULL值时导致结果为空的问题，逻辑更直观安全
• 直接判断当前用户不存在任何pizza的记录，无需额外分组

场景4：筛选完全不喜欢pizza和cake的用户的所有行 #

需求：用户的所有食物记录中既没有pizza也没有cake（如matt的apples、tacos行）

简化SQL

SELECT *
FROM t
WHERE NOT EXISTS (
    SELECT 1
    FROM t t2
    WHERE t2.name = t.name AND t2.food IN ('pizza', 'cake')
);

简化思路：

• 同样用NOT EXISTS替代NOT IN，规避NULL值陷阱
• 直接判断当前用户不存在任何pizza或cake的记录，逻辑清晰易懂

通用简化总结 #

1. 不要提前过滤分组数据：判断用户全量偏好时，子查询不要先过滤食物类型，否则会丢失关键判断依据
2. 用聚合函数简化逻辑：
   • 仅包含指定食物：优先用EVERY()（PostgreSQL）或CASE+MIN/MAX组合（通用）
   • 仅包含单个食物：用COUNT(DISTINCT food)=1结合食物值验证
3. 优先使用EXISTS/NOT EXISTS：比IN/NOT IN更安全，避免NULL值引发的异常，逻辑也更直观

内容的提问来源于stack exchange，提问作者Uk rain troll