你可以用 exists :

select t.*
from table1 t
where
    t.capatibilty in ('Grinding', 'Milling')
    and exists (
        select 1
        from table1 t1
        where 
            t1.supplier = t.supplier 
            and t1.capatibilty in ('Grinding', 'Milling')
            and t1.capability <> t.capability
    )

如果研磨和铣削是仅有的两个可能值，则可以稍微简化：

select t.*
from table1 t
where
    exists (
        select 1
        from table1 t1
        where 
            t1.supplier = t.supplier 
            and t1.capability <> t.capability
    )

如果要返回所有列，可以使用 EXISTS :

select t.*
from tablename as t
where exists (
  select 1 from tablename
  where supplier = t.supplier and capability <> t.capability
)

或：

select * from tablename
where supplier in (
  select supplier from tablename
  group by supplier
  having count(*) = 2
)

如果您只想找出具有这两种功能的供应商，您可以将表加入到表中，如下所示：

SELECT Table1.Supplier
FROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.Supplier = Table1_1.Supplier
WHERE Table1.Capability="Grinding" AND Table1_1.Capability="Milling"

但你的结果是：

A
B

如果要在单独的行中包含这些功能，则必须再次将表链接到自身，如下所示：

SELECT Table1.Supplier, Table1_2.Capability
FROM (Table1 INNER JOIN Table1 AS Table1_1 ON Table1.Supplier = Table1_1.Supplier) 
INNER JOIN Table1 AS Table1_2 ON Table1.Supplier = Table1_2.Supplier
WHERE Table1.Capability="Grinding" AND Table1_1.Capability="Milling"

结果是：

A   Milling
A   Grinding
B   Milling
B   Grinding