这里有一种方法 conditional aggregation :

select name
from yourtable
group by name
having count(case when vehicle = 'Car' then 1
                  when vehicle = 'Motorcycle' then 1
             end) = count(*)

小提琴演示示例
如果你可以有人有一辆以上的汽车或摩托车，你不想被退回（即p8有2辆车），使用 count(distinct vehicle) 而是排除那些记录。不清楚你的张贴，如果这是重要的或没有。

我会用 NOT EXISTS :

select t.*
from table t
where not exists (select 1 
                  from table t1 
                  where t1.name = t.name and 
                        t1.Vehicle in ('Helicopter', 'Truck')
                 );

您可以尝试此查询：

select Name from table 
where Name  not in (select distinct Name from table where Vehicle in 
('Helicopter','Truck'))

返回人员 EXCEPT 拥有非汽车或摩托车类型车辆的人。

select name from tablename
except
select name from tablename where vehicle not in ('Car', 'Motorcycle')

额外的 WHERE 条款可能会加速：

select name from tablename where vehicle in ('Car', 'Motorcycle')
except
select name from tablename where vehicle not in ('Car', 'Motorcycle')

我会这样写：

select name
from yourtable
where vehicle in ('car', 'motorbike')
group by name
having count(*) = 2;

这假设每个类型只有一行。如果没有，使用 count(distinct) :

select name
from yourtable
where vehicle in ('car', 'motorbike')
group by name
having count(distinct vehicle) = 2;