MySQL - 将 OR 更改为 AND

Question

假设我有以下查询:

SELECT Ads.AdId 
  FROM Ads
     , AdsAmenities 
 WHERE AdsAmenities.amenities_AmenityId IN (2, 18, 1) 
   AND Ads.AdId = AdsAmenities.ads_AdId

此查询从 Ads 表中选择每个 AdId，其中至少一个以下三个便利设施 (2, 18, 1)。 AdsAmenities 表就是其中一个，我在其中存储了 Ads 和 Amenities 之间的关系。我该如何更改此查询，使其只返回具有所有枚举便利设施 ID 的 AdId？

Answer 1

你可以尝试有:

SELECT Ads.AdId 
FROM Ads 
JOIN AdsAmenities ON Ads.AdId = AdsAmenities.ads_AdId 
WHERE AdsAmenities.amenities_AmenityId IN (2, 18, 1) 
GROUP BY Ads.AdId
HAVING COUNT(distinct AdsAmenities.amenities_AmenityId) = 3

如果 AdsAmenities.amenities_AmenityId 值是唯一的，您可以跳过 distinct 部分。