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

I have some models as follows:

我有一些模型如下:

Product:
  id
  name
  price

Order:
  id
  user
  created

OrderProduct:
  order_id
  product_id

Promo:
  id
  discount

PromoProduct:
  promo_id
  product_id

A promotion applies to an order if all products in the promotion are present in the order. How do I write a SQL query to find out what promotions apply to an order?

如果促销活动中的所有产品都在订单中，则促销活动适用于订单。如何编写一个SQL查询，以查明哪些促销活动适用于订单?

5 个解决方案

#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
                )
        )

Note that this will also return all promotions with empty product lists ("there ain't no plane in this hangar that I can't fly"). If that's a problem, you will need to throw in an additional check for non-emptiness of the promotions.

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

#2

This should fulfil your criterion that all the products in the promo need to be part of the order - because of the q1.ct = q2.ct. q1.ct gives you a count of products per promo per order; q2.ct gives you a total count of products per order.

这应该符合你的标准，所有的产品都必须是订单的一部分-因为q1。ct = q2.ct。q1。ct为您提供每个订单的产品数量;q2。ct给出每个订单的产品总数。

Obviously you can further join this to the Order and Promo tables to get whatever additional information you want in your result set.

显然，您可以将它进一步连接到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

#3

Using subqueries you can divide and conquer.

使用子查询可以分割和征服。

Create a query to find how many products are in each promo:

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

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

Then create another to find how many products for each order are in a promo:

然后创建另一个，以找出每个订单有多少产品在促销:

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

Then join them together:

然后将它们连接在一起:

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

That may need a little tweaking but you get the point.

这可能需要一些调整，但你明白了。

#4

You could left join a given promo's products to a given order's products, and if any orderproducts row(s) came back null it would mean that the order did not qualify for that promo, given your definition of what it takes to qualify for a promo.

您可以将一个给定的promo的产品添加到一个给定的订单的产品中，如果任何orderproducts row(s)返回null，那么就意味着该订单没有资格获得该promo，因为您定义了它需要什么才能获得promo。

EDIT:

编辑:

        select promoproducts.productid, orderproducts.productid
        from promoproducts
        left join orderproducts
        on promoproducts.productid = orderproducts.productid
        where promoid = 'x' and orderid = 'y'

EDIT2: ignore above (see Quassnoi's comment); the approach I'm suggesting needs to be done as a LEFT JOIN to an inline view:

EDIT2:忽略上面的内容(参见Quassnoi的评论);我所建议的方法需要作为内联视图的左连接来完成:

        select pp.productid as promoproduct, orderproduct
        from promoproducts pp
        left join 

          (
         select productid as orderproduct
           from orderproducts
           where orderid=1

         ) as op
         on pp.productid=op.orderproduct
          where promoid =1

EDIT3: instead of comparing the order to a specific promo (where promoid = x), it could be compared against all current promos:

EDIT3:与其将顺序与特定的promo(其中promoid = x)进行比较，还不如将它与所有当前的promo进行比较:

          ...  where promoid in (select promoid from promos where startdate ... expirydate ...) 
           -- find current promos

The real-world advantage to this left-join approach is that it shows you all of the promos the order qualifies for as well as all of the promos it does not qualify for and which product(s) would have to be added to the order to qualify for a promo.

这种左连接方法的实际好处是，它向您展示了订单所限定的所有促销活动，以及它不符合的所有促销活动，以及需要将哪些产品添加到订单中才能获得促销活动。

#5

SELECT *
FROM 'order' o
INNER JOIN orderproduct op ON op.order_id = o.order_id
INNER JOIN PromoProduct p ON p.product_id = op.product_id
WHERE o.order_id = ?

Try that.. Though I think you should rename the order table, as it is a keyword in MySQL.

试试. .尽管我认为应该重命名order表，因为它是MySQL中的关键字。