mysql查第二行,MYSQL查找第二行具有给定值的所有行

my database table looks like:

╔════╦═══════════╦═══════════╗

║ ID ║ RECIPE_ID ║ NAME ║

╠════╬═══════════╬═══════════╣

║ 1 ║ 1 ║ Apple ║

║ 2 ║ 2 ║ Apple ║

║ 3 ║ 2 ║ Orange ║

║ 4 ║ 3 ║ Kiwi ║

║ 5 ║ 1 ║ Kiwi ║

║ 6 ║ 3 ║ Cherry ║

║ 7 ║ 3 ║ Banana ║

╚════╩═══════════╩═══════════╝

When i'm querying mysql for "Apple" AND "Orange", so i should get the RECIPE_ID 2 because "Apple" and "Orange" have the same RECIPE_ID

or second example:

When looking for "Kiwi" AND "Banana" i should get the RECIPE_ID 3

Here is my SQL I have tried

SELECT recipe_id, name

FROM foodtipps.rezepte_zutaten

WHERE name='Kiwi' AS 'NAME1' AND

name='Banana AS 'NAME2' GROUP BY recipe_id

Hope you understand my problem.

Thank you!

解决方案

This can be extended to many more ingredients easily:

SELECT recipe_id

FROM theTable

WHERE name IN ('Apple', 'Orange')

GROUP BY recipe_id

HAVING COUNT(*) = 2 /* number of ingredients in the list */