sql - 如何选择属于一组但不属于另一组的项目

Question

Sqlite3
我如何选择只作为宠物而不是食物的动物？ POF 是“宠物或食物”栏。一个动物可以同时属于这两个群体。这是实际问题的较小版本。我不想把它分成更多的表。

animal  pof
----------
fish    pet
fish    food
pig     food
cat     pet
dog     pet
horse   pet
mouse   pet
duck    pet
duck    food
cow     food
rabbit  pet
rabbit  food
gerbil  pet
worm    <null>
chicken food

我有以下内容，但似乎很尴尬:

SELECT * from 
(SELECT  NAME, POF, count(*) as cnt
 FROM    ANIMALS
 GROUP BY NAME) AS GC
 WHERE GC.cnt == 1 AND GC.POF == 'pet'

正确屈服:

NAME    POF cnt
---------------
cat     pet  1
dog     pet  1
gerbil  pet  1
horse   pet  1
mouse   pet  1

Answer 1

使用 NOT IN排除所有具有 pof = 'food' 的动物:

select *
from animals
where pof = 'pet'
and animal not in (select animal from animals where pof = 'food')

或者，如果您只想要列 animal您可以使用 EXCEPT :

select animal from animals where pof = 'pet'
except
select animal from animals where pof = 'food'

见 demo .