以及像IN子句这样的多个值

Question

如何获得同时具有Feature1&下表中的功能2（宝马和丰田）？

Select Car,Feature 
    from Table 
    where Feature in ('Feature1','Feature2') 

提供了所有3辆车，包括只有Feature1的本田。这只是实际查询的一个简化示例，in子句中可以有数百个值。

Car    Feature
-----  --------
BMW    Feature1
BMW    Feature2
BMW    Feature3
Toyota Feature1
Toyota Feature2
Honda  Feature1

谢谢，Kiran

Answer 1

select Car
from your_table
where feature in ('f1', 'f2')
group by car
having count(distinct feature) >= 2

Answer 2

使用GROUP/HAVING构造，其中HAVING测试in子句中的元素数量。这样可以保证这两个功能都存在。

SELECT cf.Car
    FROM CarFeature cf
    WHERE cf.Feature IN ('Feature1', 'Feature2')
    GROUP BY cf.Car
    HAVING COUNT(DISTINCT cf.Feature) = 2;

Answer 3

IN子句实际上是嵌套的OR。下面将为您介绍所有具有这两种功能的汽车。

SELECT U.Car FROM
  (
   Select Car from Table where Feature ='Feature1'
   UNION ALL
   Select Car from Table where Feature ='Feature2'
  ) AS U
WHERE COUNT(U.Car) > 1