显示配方名称(如果已提供所有成分的名称)

本文介绍了显示配方名称(如果已提供所有成分的名称)的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有3个具有这种结构的表:

I have 3 tables with this structure:

食谱

id |食谱名称

成分

id | Ingredients_name

id | ingredients_name

关系

rid | recipe_id | Ingredients_id

rid | recipe_id | ingredients_id

示例i搜索成分为鸡肉"和蛋黄酱"的食谱，然后它将显示所有包含这两种成分的食谱.我已经获得了代码:

Example i search for recipe's with an ingredient of "chicken" and "mayonnaise" then it will display all the recipe's having this two ingredients. i already get the code for that:

SELECT * from recipe r INNER JOIN relationship ri on r.id=ri.recipe_id INNER JOIN ingredients i on i.id=ri.ingredients_id WHERE i.ingredients_name IN ('chicken','mayonnaise') GROUP BY r.id

但是我想要的是，当一个食谱仅包含鸡肉和蛋黄酱作为其成分时，它必须在单独的结果中显示.我该怎么办?

But what I want is that when a recipe contains only chicken and mayonnaise as its ingredients, it must display on a separate result. How can i do that?

推荐答案

通过添加HAVING子句-像这样:

By adding a HAVING clause - like so:

SELECT * from recipe r INNER JOIN relationship ri on r.id=ri.recipe_id INNER JOIN ingredients i on i.id=ri.ingredients_id GROUP BY r.id HAVING COUNT(DISTINCT i.ingredients_name)=2 AND COUNT(DISTINCT case when i.ingredients_name IN ('chicken','mayonnaise') then i.ingredients_name end)=2

(注意:这将返回仅包含鸡和蛋黄酱且不包含其他成分的食谱.要返回包含鸡和蛋黄酱但也可能包含其他成分的食谱，请移除COUNT(DISTINCT case条件并恢复WHERE子句.)

(Note: this will return recipes that only include both chicken and mayonnaise, and no other ingredients. To return recipes that include both chicken and mayonnaise, but that could also include other ingredients, remove the COUNT(DISTINCT case condition and reinstate the WHERE clause.)