MYSQL查找第二行具有给定值的所有行 [英] MYSQL find all rows where second row have given values

问题描述

我的数据库表如下:

╔════╦═══════════╦═══════════╗
║ 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    ║
╚════╩═══════════╩═══════════╝

当我向mysql查询"Apple"和"Orange"时，我应该得到RECIPE_ID 2，因为"Apple"和"Orange"具有相同的RECIPE_ID 或第二个示例:

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:

寻找"Kiwi"和"Banana"时，我应该得到RECIPE_ID 3

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

这是我尝试过的SQL

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 */

这篇关于MYSQL查找第二行具有给定值的所有行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！