我有桌子dishes，ingredients和dishes_ingredients。我想展示所有带有mango和的菜肴chicken。我可以通过首先获取所有包含的菜肴mango，然后选择chicken这些结果中也包含的所有菜肴来实现。像这样：
SELECT e_dishes.id, e_dishes.name FROM
(
SELECT DISTINCT dishes.id, dishes.name FROM dishes
JOIN dishes_ingredients ON dishes.id = dishes_ingredients.dishes_id
JOIN ingredients ON dishes_ingredients.ingredients_id = ingredients.id
WHERE ingredients.name = 'mango'
) AS e_dishes
JOIN dishes_ingredients ON e_dishes.id = dishes_ingredients.dishes_id
JOIN ingredients ON dishes_ingredients.ingredients_id = ingredients.id
WHERE ingredients.name = 'chicken';
但这似乎还不够优雅。我认为，必须有一种方式来获得一个包含所有菜肴mango，并chicken在短短的一个查询。
您可以使用聚合：
SELECT d.id, d.name
FROM dishes d JOIN
dishes_ingredients di
ON d.id = di.dishes_id JOIn
ingredients i
ON di.ingredients_id = i.id
WHERE i.name IN ('mango', 'chicken')
GROUP BY d.id, d.name
HAVING COUNT(*) = 2; -- number of ingredients
假设没有列出相同的成分两次。如果是这样，请使用having count(distinct i.name) = 2。
请注意使用表别名，因此查询更易于编写和阅读。
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句