MySQL - 将 OR 更改为 AND [英] MySQL - change OR to AND

问题描述

假设我有以下查询:

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?

This query selects each AdId from the Ads table, which has at least one of the following three amenities (2, 18, 1). The AdsAmenities table is the one, where I store the relation between Ads and Amenities. How could I change this query, that it should return only the AdId's which have all of the enumerated amenity ids?

推荐答案

你可以试试have:

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 值为 uniq，您可以跳过 distinct 部分.

In case AdsAmenities.amenities_AmenityId values are uniq, you can skip the distinct part.

这篇关于MySQL - 将 OR 更改为 AND的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！