用于按“和”条件在单个列中选择值的SQL

Question

我有一个类似于贝娄的表格数据

我需要一个sql查询，返回同时吃胡萝卜和苹果的PersonId的总数。在上面的示例中，结果是，结果：2。（PersonId's 111和222）

一个ms-sql查询，如“从吃=‘胡萝卜’和吃=‘苹果’的人中选择计数（不同的人身份）”

Answer 1

可以使用排序的条件聚合：

select
personid
from <yourtable>
group by 
personid
having
count (case when eat = 'carrot' then 1 else null end) >= 1
and count (case when eat = 'apple' then 1 else null end) >= 1

Answer 2

SELECT PersonID FROM Person WHERE Eat = 'Carrot'
INTERSECT
SELECT PersonID FROM Person WHERE Eat = 'Apple'

Answer 3

实际上，您无需使用子查询来确定同时吃这两种食物的人，就可以获得计数。假设行是唯一的：

select ( count(distinct case when eat = 'carrot' then personid end) +
         count(distinct case when eat = 'apple' then personid end) -
         count(distinct personid)
       ) as num_both
from t
where eat in ('carrot', 'apple')