mysql - 给定单个字段中的内容，如何执行在同一行中的不同字段中传递值的查询？

Question

member  purchase   fruit
1         1        apple
1         2        apple
1         3        banana
1         4        banana
2         1        apple
2         2        apple
2         3        banana
2         4        banana

如何生成以下结果集？

SELECT * FROM table wherefruit =(记录中的水果，其中member = ?且purchase = ?)

例如，如果 member = 2 且 purchase = 3，结果集将为

member  purchase   fruit
2         3        banana
2         4        banana

Answer 1

SELECT * FROM table where fruit = (the fruit in the record where member = ? and purchase = ?)

你快要拥有它了。试试这个:

SELECT * FROM yourtable WHERE fruit =
(
     SELECT fruit FROM yourtable WHERE member = ? AND purchase = ?
)

您可能还想将 AND member = ? 添加到查询末尾，正如 Jonas Heidelberg 在评论中提到的那样。