使用 SQL 查询多对多关系表，根据多个条件筛选结果
2023-10-31 14:09:19
要查询包含所有指定食材的菜谱，可使用以下 SQL 语句：
SELECT r.id, r.nameFROM recipe rJOIN recipe_ingredient ri ON r.id = ri.ridJOIN ingredient i ON i.id = ri.iidWHERE i.name IN ('egg', 'milk') -- 精确匹配指定食材名称GROUP BY r.id, r.name -- 分组依据需包含所有非聚合列HAVING COUNT(DISTINCT i.id) = 2; -- 条件数量需与参数数量一致关键步骤解析表连接通过 JOIN 将三张表关联：
recipe 与 recipe_ingredient 通过 r.id = ri.rid 关联，获取菜谱与食材关系。
recipe_ingredient 与 ingredient 通过 i.id = ri.iid 关联，获取食材名称。
条件筛选
WHERE i.name IN ('egg', 'milk') 精确匹配指定食材名称（比 LIKE 更高效且避免误匹配）。
若需模糊匹配（如包含关键词），可改用 WHERE i.name LIKE '%egg%' OR i.name LIKE '%milk%'，但需调整后续逻辑。
分组与聚合
GROUP BY r.id, r.name 按菜谱分组，确保每个菜谱仅出现一次。
HAVING COUNT(DISTINCT i.id) = 2 筛选分组后满足条件的菜谱：
COUNT(DISTINCT i.id) 统计每个菜谱关联的不同食材数量。
= 2 表示仅保留同时包含两种指定食材的菜谱（参数数量需动态调整）。
若需查询任意数量的指定食材（如 N 种），需：
- 将 WHERE 子句中的条件改为动态参数列表（如 i.name IN ('egg', 'milk', 'sugar')）。
- 修改 HAVING 子句中的数值为参数数量（如 = 3）。
示例（查询包含 egg、milk、sugar 的菜谱）：
SELECT r.id, r.nameFROM recipe rJOIN recipe_ingredient ri ON r.id = ri.ridJOIN ingredient i ON i.id = ri.iidWHERE i.name IN ('egg', 'milk', 'sugar')GROUP BY r.id, r.nameHAVING COUNT(DISTINCT i.id) = 3;注意事项性能优化
确保 ingredient.name 和 recipe_ingredient.rid/iid 字段有索引，加速连接与筛选。
避免在 WHERE 中使用 OR 连接多个 LIKE，可能导致索引失效。
结果准确性
DISTINCT 确保同一食材多次出现时仅计数一次（如菜谱需 2 个鸡蛋 + 1 杯牛奶，仍符合条件）。
若需严格匹配食材数量（如恰好 2 种食材，不多不少），需额外排除其他食材：SELECT r.id, r.nameFROM recipe rJOIN recipe_ingredient ri ON r.id = ri.ridJOIN ingredient i ON i.id = ri.iidWHERE i.name IN ('egg', 'milk')GROUP BY r.id, r.nameHAVING COUNT(DISTINCT i.id) = 2AND NOT EXISTS ( -- 排除包含其他食材的菜谱 SELECT 1 FROM recipe_ingredient ri2 JOIN ingredient i2 ON ri2.iid = i2.id WHERE ri2.rid = r.id AND i2.name NOT IN ('egg', 'milk'));
通过 JOIN 关联多表、WHERE 筛选候选记录、GROUP BY 分组及 HAVING COUNT() 精确计数，可高效查询多对多关系中满足所有条件的记录。此方法避免了多次查询或应用层处理，适用于复杂筛选场景。
热门标签
