mysql - SQL 查询仅获取所有请求的 2 列匹配发生的结果

Question

我想要得到一个结果，其中第 1 列的值可以在第 2 列和第 3 列的三个(或更多)组合中找到(成对？)示例...这是一个包含 3 列的假设表格:

FRUIT   |  TRAIT    |   DESCRIBED-------------------------------Apple   |  color    |  red Apple   |  shape    |  roundPear    |  surface  |  smooth pear    |  stem     |  red grape   |  shape    |  round grape   |  color    |  red grape   |  surface  |  smooth

I want to find fruit that have a shape=round, are color=red and surfaced=smooth. The result in the above table should be "grape", but my attempts at figuring out the right sql query, I can't narrow it down. I get Apple, because it's red, and pear because it's smooth. But I don't want those. I want a fruit that shows up for all three trait/described values.

I've gotten this far, thinking there was a way to use group, and I used Subqueries - thinking that would give me a way to require. But it still doesn't give me just 'grape' in results.

 SELECT fruit /* DISTINCT ?*/ FROM table1 WHERE fruit 
 IN  (SELECT fruit FROM table1 WHERE trait = 'shape' AND described = 'round')
 AND (SELECT fruit FROM table1 WHERE trait = 'color' AND described = 'red')
 AND (SELECT fruit FROM table1 WHERE trait = 'surface' AND described = 'smooth')     
 HAVING count(fruit) = 3   /* or whatever number of trait/described combos were searched for */

Answer 1

你太接近了。只需继续从子句中选择水果，不用担心计数 - 您使用 DISTINCT 也是正确的

SELECT DISTINCT FRUIT FROM Table1 
WHERE FRUIT IN (SELECT FRUIT FROM Table1 WHERE TRAIT = 'shape' AND DESCRIBED = 'round')
AND FRUIT IN (SELECT FRUIT FROM Table1 WHERE TRAIT = 'color' AND DESCRIBED = 'red')
AND FRUIT IN (SELECT FRUIT FROM Table1 WHERE TRAIT = 'surface' AND DESCRIBED = 'smooth')

您可以在 SQL Fiddle I used 上进行测试