选择不同行中具有匹配条件的记录

SELECT person 
FROM table1
WHERE eats IN( 'apple', 'strawberry' )
GROUP BY person
HAVING COUNT(DISTINCT eats)=2

使现代化 对我来说,这个问题本身就解释了。但既然你要求解释,让我们一起尝试:

原始查询返回以下结果:

1
1
4

person ,这将我们带到下一个查询:

SELECT person 
FROM table1
WHERE eats IN( 'apple', 'strawberry' )
GROUP BY person

其将返回:

1
4

但你不想 person=4 apple 因此,对我来说,最简单的方法是计算不同 eats 比如:

SELECT person, count(distinct eats)
FROM table1
WHERE eats IN( 'apple', 'strawberry' )
GROUP BY person

然后我们得到这个结果:

person  count(distinct eats)
1        2
4        1

我们已经准备好过滤这个结果集,只得到那些有2个(苹果和草莓)的人:

SELECT person, count(distinct eats)
FROM table1
WHERE eats IN( 'apple', 'strawberry' )
GROUP BY person
HAVING count(distinct eats) = 2

这将返回:

person  count(distinct eats)
1        2

但你没有要求 count 这就是为什么我把它去掉了 count(distinct eats) 离开 SELECT ed字段列表。

http://sqlfiddle.com/#!9/ea612/6

分组 person 只拿那些有 二者都 组中的记录

select person 
from table1
where eats in( 'apple', 'strawberry' )
group by person
having count(*) = 2