找到一个吃过苹果但没有吃过香蕉的人

Question

我想设置一个SQLite查询，以确定患有某种person_id的人是否吃了Fruit'apple'，但没有吃水果'banana'。我可以访问Fruit表格，其中包含关于谁吃过哪种水果的信息。然后，我想将此信息用于SELECT，即通过子查询吃apples但不吃bananas的人的姓氏。在我看来，它会变成这样：

SELECT
P.last_name
FROM
Person AS P
WHERE
P.pid IN (
SELECT
F1.pid
FROM
Fruit AS F1,
Fruit AS F2
WHERE
F1.pid = F2.pid
AND F1.type = 'apple'
AND F2.type <> 'banana'
)
ORDER BY P.pid;

然而，这还不是一个足够严格的条件，因为现在我允许在F2中使用与F1中相同的水果类型，这仍然可以选择那个人。另一方面，我不能过多地限制这一点，例如

F1.type <> F2.type

在子查询中，因为这将把合法人员从SELECTed的人中赶出去。

要使这项工作发挥作用，必须有什么限制？

Answer 1

SELECT
P.last_name
FROM
Person AS P
WHERE
P.pid IN (
SELECT
F1.pid
FROM Fruit AS F1
LEFT JOIN Fruit AS F2 ON F2.type = 'banana' AND F1.pid = F2.pid
WHERE
F1.type = 'apple'
AND F2.pid IS NULL
)
ORDER BY P.pid;

Answer 2

使用聚合：

select p.lastname
from person p
where p.fruit in ('apple', 'banana')
group by p.lastname
having min(p.fruit) = max(p.fruit) and min(p.fruit) = 'apple';

where将查询限制为仅考虑苹果和香蕉。having条款说只有一种水果。那个水果是一个苹果。

Answer 3

主查询选择吃苹果的人。

NOT EXISTS不包括吃香蕉的人。

SELECT p.last_name
FROM person p
INNER JOIN fruit apple ON apple.pid = p.pid
WHERE apple.type = 'apple'
AND NOT EXISTS (
SELECT 1
FROM fruit banana
WHERE banana.pid = p.pid
AND banana.type = 'banana'
)