多表关联SQL查询：如何获取同时包含食材A和食材B的菜品名称

嘿，这个问题太常见啦！你说得对，直接加AND ingredient.name="B"肯定行不通——毕竟单条记录里只能对应一种食材嘛。下面给你三种实用的解法，每种都有清晰的逻辑和代码示例：

这种方法适合需要匹配多种食材的场景（比如以后要找同时含A、B、C的菜品，改起来超方便）。思路是先筛选出和目标食材相关的记录，再按菜品分组，确保每组包含所有需要的食材：

SELECT d.name
FROM dishes d
JOIN recipes r ON d.id = r.id_dish
JOIN ingredient i ON r.id_ingredient = i.id
WHERE i.name IN ('A', 'B') -- 先过滤出只和A/B相关的记录
GROUP BY d.id, d.name       -- 按菜品分组（用id分组避免重名菜品干扰）
HAVING COUNT(DISTINCT i.name) = 2; -- 确保分组里有2种不同的食材（A和B）

为什么用DISTINCT？ 防止某道菜里重复使用同一种食材（比如加了两次A），导致统计数量虚高，确保确实同时包含两种不同的食材。

如果只需要匹配两种食材，这种写法更直观——相当于让菜品同时“关联到A的食材记录”和“关联到B的食材记录”：

SELECT d.name
FROM dishes d
-- 先关联到含食材A的记录
JOIN recipes r1 ON d.id = r1.id_dish
JOIN ingredient i1 ON r1.id_ingredient = i1.id AND i1.name = 'A'
-- 再关联到含食材B的记录
JOIN recipes r2 ON d.id = r2.id_dish
JOIN ingredient i2 ON r2.id_ingredient = i2.id AND i2.name = 'B';

这种写法逻辑直白，一眼就能看出来是在找同时满足两个关联条件的菜品，缺点是如果要加更多食材，就得继续加JOIN，代码会变长。

如果你的数据表数据量比较大，这种方法可能更高效——因为EXISTS是半连接，只要找到匹配的记录就会停止查询，不用扫描全表统计：

SELECT d.name
FROM dishes d
-- 检查菜品是否包含食材A
WHERE EXISTS (
    SELECT 1
    FROM recipes r
    JOIN ingredient i ON r.id_ingredient = i.id
    WHERE r.id_dish = d.id AND i.name = 'A'
)
-- 同时检查菜品是否包含食材B
AND EXISTS (
    SELECT 1
    FROM recipes r
    JOIN ingredient i ON r.id_ingredient = i.id
    WHERE r.id_dish = d.id AND i.name = 'B'
);

这种写法可读性也很强，每个子查询单独验证一个食材条件，两个条件都满足才会选中菜品。

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