PostgreSQL中基于连接表筛选完全匹配食材的披萨查询问题

你的需求核心是找出所有食材都完全包含在你拥有的食材列表中的披萨，以下是几种高效且符合要求的实现方式：

方案一：分组统计匹配法 #

通过统计披萨的总食材数，以及其中属于拥有列表的食材数，当两者相等时，说明该披萨的所有食材都满足条件：

SELECT p.id, p.name
FROM Pizza p
JOIN PizzaIngredients pi ON p.id = pi.pizzaId
GROUP BY p.id, p.name
-- 替换括号内的数字为你实际拥有的食材ID列表
HAVING COUNT(*) = COUNT(CASE WHEN pi.ingredientId IN (1, 3) THEN 1 END);

方案二：NOT EXISTS 排除法（性能优先） #

直接筛选「不存在任何一种食材不在拥有列表中」的披萨，PostgreSQL对这类查询优化较好，大表场景下效率更高：

SELECT p.id, p.name
FROM Pizza p
WHERE NOT EXISTS (
    SELECT 1
    FROM PizzaIngredients pi
    WHERE pi.pizzaId = p.id
    -- 替换括号内的数字为你实际拥有的食材ID列表
    AND pi.ingredientId NOT IN (1, 3)
);

方案三：PostgreSQL数组特性写法（语法简洁） #

利用PostgreSQL的数组子集运算符<@，判断披萨的食材数组是否是拥有列表数组的子集：

SELECT p.id, p.name
FROM Pizza p
JOIN (
    SELECT pizzaId, ARRAY_AGG(ingredientId) AS ingredients
    FROM PizzaIngredients
    GROUP BY pizzaId
) pi ON p.id = pi.pizzaId
-- 替换ARRAY内的数字为你实际拥有的食材ID列表
WHERE pi.ingredients <@ ARRAY[1, 3]::INT[];

你原有SQL的问题 #

1. 字段名错误：pi.ingredientsId应为pi.ingredientId（多了一个s）；
2. 逻辑反向：你的语句仅筛选出「存在食材在列表中」的披萨记录，但未排除「同时包含不在列表里的食材」的披萨，所以会错误返回披萨2、3。

内容的提问来源于stack exchange，提问作者loic.k