SQL等效于IN运算符，用作AND而不是OR？ [英] SQL equivalent of IN operator that acts as AND instead of OR?

问题描述

我不会描述，而是简单地展示我想做的事情。 3NF中有3张桌子。 product_badges是联接表。 （存在子查询是必需的。）

Rather than describe, I'll simply show what I'm trying to do. 3 tables in 3NF. product_badges is the join table. (The exists sub-query is necessary.)

SELECT * FROM shop_products WHERE EXISTS ( // this line cannot change
    SELECT * FROM product_badges as pb 
    WHERE pb.product_id=shop_products.id
    AND pb.badge_id IN (1,2,3,4)
);

现在，这将返回所有badge_id为1或2或3或4的产品。我要仅获取满足所有这些值的产品。我试图做pb.badge_id = 1和pb.badge_id = 2等，但这什么也没返回-现在对我来说有意义。我还尝试使用INTERSECT进行多个查询，但这导致了错误。我猜多个查询是关键，但是UNION与IN基本相同，并且在这种情况下我不确定如何使用JOIN。

Now this will return all the products that have a badge_id of 1 OR 2 OR 3 OR 4. What I want is to get only the products that meet ALL those values. I tried to do pb.badge_id=1 AND pb.badge_id=2 etc but this returns nothing -- which makes sense to me now. I also tried doing multiple queries with an INTERSECT but that resulted in an error. I'm guessing multiple queries is the key but UNION is basically the same as IN and I'm not certain how to use a JOIN in this case.

推荐答案

请尝试以下操作（假设恰好有4个不同的bagde_id）：

Please try the following (assuming exactly 4 different required bagde_id's):

SELECT * FROM shop_products WHERE EXISTS ( // this line cannot change
    SELECT pb.product_id, count(distinct pb.bagde_id) FROM product_badges as pb 
    WHERE pb.product_id=shop_products.id
    AND pb.badge_id IN (1,2,3,4)
    GROUP BY pb.product_id
    HAVING count(distinct pb.bagde_id) = 4
);

这篇关于SQL等效于IN运算符，用作AND而不是OR？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！