您可以使用聚合和having来过滤：

select person_id
from car
group by person_id
having count(*) = 1              -- has just one car
   and max(car = 'Mazda') = 0    -- which is not a Mazda

我们可以直接从car表中获得结果。如果出于某种原因，需要从person中取出相应的行，我们可以join：

select p.*
from person p
inner join (
    select person_id
    from car
    group by person_id
    having count(*) = 1 and max(car = 'Mazda') = 0
) c on c.person_id = p.id

另一种选择是，在相关子查询上使用EXISTS运算符的否定，如下所示：

select * -- you may replace * with your columns list
from Car t1 
where car <> 'Mazda' and
  not exists(
  select 1 from Car t2 
  where t1.person_id = t2.person_id and
        t1.car <> t2.car)

demo