如何编写SQL查询来检查一个表上的多对多关系是否是另一个表上的多对多关系的子集？

Question

我有一些模型如下：

Product:
  id
  name
  price

Order:
  id
  user
  created

OrderProduct:
  order_id
  product_id

Promo:
  id
  discount

PromoProduct:
  promo_id
  product_id

如果促销中的所有产品都包含在订单中，则促销适用于该订单。如何编写SQL查询以找出适用于订单的促销？

Answer 1

SELECT  *
FROM    promo p
WHERE   id NOT IN
        (
        SELECT  pp.promo_id
        FROM    promoProduct pp
        WHERE   pp.product_id NOT IN
                (
                SELECT  product_id
                FROM    orderProduct op
                WHERE   op.order_id = $order
                )
        )

请注意，这也将返回所有带有空产品列表的促销活动(“这个机库中没有我不能驾驶的飞机”)。如果这是一个问题，你将需要抛出一个额外的检查非空的促销。

Answer 2

这应该满足您的标准，即促销中的所有产品都需要是订单的一部分-因为q1.ct = q2.ct。q1.ct为您提供每个订单的每个促销活动的产品计数；q2.ct为您提供每个订单的产品总数。

显然，您可以进一步将其连接到Order和Promo表中，以获得您想要的结果集中的任何其他信息。

SELECT q1.order_id, q1.promo_id FROM
( SELECT op.order_id, pp.promo_id, COUNT( op.product_id ) AS ct
FROM OrderProduct op INNER JOIN PromoProduct pp ON product_id
GROUP BY op.order_id, pp.promo_id ) q1

INNER JOIN

( SELECT pp.promo_id, COUNT( pp.product_id ) AS ct
FROM PromoProduct pp
GROUP BY pp.promo_id ) q2

ON q1.promo_id = q2.promo_id AND q1.ct = q2.ct

Answer 3

使用子查询，您可以分而治之。

创建一个查询，以查找每个促销活动中有多少产品：

SELECT promo_id, COUNT(product_id) AS no_products
FROM PromoProduct
GROUP BY promo_id

然后创建另一个，以确定促销活动中每个订单的产品数量：

SELECT OP.order_id, PP.promo_id, COUNT(OP.product_id) AS no_products
FROM OrderProduct OP
INNER JOIN PromoProduct PP ON OP.product_id = PP.product_id
GROUP BY OP.order_id, PP.promo_id

然后将它们连接在一起：

SELECT OP.order_id, PP.promo_id
FROM OrderProduct OP
INNER JOIN PromoProduct PP ON OP.product_id = PP.product_id
INNER JOIN (SELECT promo_id, COUNT(product_id) AS no_products
FROM PromoProduct
GROUP BY promo_id) PC ON PP.promo_id = PC.promo_id AND COUNT(OP.product_id) = PC.no_products
GROUP BY OP.order_id, PP.promo_id

这可能需要稍微调整一下，但你已经明白了。