如果表1中的* all *行不存在，则MySQL查询从表2中选择行

I'm doing a kind of point-of-sale system whose MySQL database has (among other things) a table with items for sale, a table with sales, and a table with purchases (a purchase being my ad-hoc notation for any single item bought in a sale; if the same person buys three items at once, for example, that's one sale consisting of three purchases). All these tables have logical IDs, viz. item_id, sale_id, purchase_id, and are easily joined with simple pivotal tables.

我正在做一种销售点系统，其MySQL数据库（除其他外）有一个带有待售物品的桌子，一张带销售的桌子和一张带有购买的桌子（购买是我的特别记录）在销售中购买的单件商品;如果同一个人一次购买三件商品，例如，这是一次包含三次购买的销售）。所有这些表都有逻辑ID，即。 item_id，sale_id，purchase_id，并且可以通过简单的关键表轻松加入。

I am now trying to add a discount feature; basically your garden-variety supermarket discount: buy these particular items and pay X instead of paying the full sum of the regular item prices. These 'package deals' have their own table and are linked to the items table with a simple pivotal table containing deal_id and item_id.

我现在正在尝试添加折扣功能;基本上你的花园品种超市折扣：购买这些特殊物品并支付X而不是支付正常项目价格的全部金额。这些“包交易”有自己的表，并通过包含deal_id和item_id的简单关键表链接到items表。

My problem is getting to the point of figuring out when this is to be applied. To give some example data:

我的问题是要弄清楚何时应用这个问题。举几个例子：

items
+---------+--------+---------+
| item_id | title  | price   |
+---------+--------+---------+
|      12 | Shoe   |      10 |
|      76 | Coat   |      23 |
|      82 | Whip   |      19 |
+---------+--------+---------+

sales
+---------+-----------+
| sale_id | timestamp |
+---------+-----------+
|    2973 | 144995839 |
|    3092 | 144996173 |
+---------+-----------+

purchases
+-------------+-------------+---------+----------+---------+
| purchase_id | no_of_items | item_id | at_price | sale_id |
+-------------+-------------+---------+----------+---------+
|       12993 |           1 |      12 |       10 |    2973 |
|       12994 |           1 |      76 |       23 |    2973 |
|       12996 |           1 |      82 |       19 |    2973 |
|       13053 |           1 |      12 |       10 |    3092 |
|       13054 |           1 |      82 |       19 |    3092 |
+-------------+-------------+---------+----------+---------+

package_deals
+---------+-------+
| deal_id | price |
+---------+-------+
|       1 |    40 |
+---------+-------+

deals_items
+---------+---------+
| deal_id | item_id |
+---------+---------+
|       1 |      12 |
|       1 |      76 |
|       1 |      82 |
+---------+---------+

As is hopefully obvious from that, we have a shoe that cost $10 (let's just assume we use dollars as our currency here, doesn't matter), a coat that costs $23, and a whip that costs $19. We also have a package deal that if you buy both a shoe, a coat, and a whip, you get the whole thing for $40 altogether.

正如希望显而易见的那样，我们的鞋子价格为10美元（我们假设我们在这里使用美元作为我们的货币，无所谓），一件价值23美元的外套，以及一件价值19美元的鞭子。我们还有一揽子协议，如果你同时购买一双鞋，一件外套和一条鞭子，你可以获得40美元的全部费用。

Of the two sales given, one (2973) has purchased all three things and will get the discount, while the other (3092) has purchased only the shoe and the whip and won't get the discount.

在给出的两个销售中，一个（2973）购买了所有三件物品并且将获得折扣，而另一个（3092）仅购买了鞋子和鞭子并且不会获得折扣。

In order to find out whether or not to apply the package-deal discount, I of course have to find out whether all the item_ids in a package deal are present in the purchases table for a given sale_id.

为了找出是否应用包裹交易折扣，我当然必须找出包裹交易中的所有item_id是否存在于给定sale_id的购买表中。

How do I do this?

我该怎么做呢？

I thought I should be able to do something like this:

我以为我应该可以这样做：

SELECT     deal_id, item_id, purchase_id
FROM       package_deals
LEFT JOIN  deals_items
    USING  (deal_id)
LEFT JOIN  purchases
    USING  (item_id)
WHERE
    sale_id = 2973
AND item_id IS NULL
GROUP BY   deal_id

In my head, that retrieved all rows from the package_deal table where at least one of the item_ids associated with the package deal in question does not have a corresponding match in the purchases table for the sale_id given. This would then have told me which packages don't apply; i.e., it would return zero rows for purchase 2973 (since none of the items associated with package deal 1 are absent from the purchases table filtered on sale_id = 2973) and one row for 3092 (since one of the items associated with package deal one—namely the coat, item_id 76—is absent from the purchases table filtered on sale_id = 3092).

在我的脑海中，它从package_deal表中检索了所有行，其中至少有一个与所涉及的包交易相关联的item_id在给定的sale_id的购买表中没有相应的匹配。这会告诉我哪些包不适用;即，它将为购买2973返回零行（因为在sale_id = 2973上过滤的购买表中没有与包裹交易1相关联的项目）和3092中的一行（因为与包裹相关的一个项目之一 - 即，在sale_id = 3092上过滤的购买表中没有外套，item_id 76-。

Obviously, it doesn't do what I naïvely thought it would—rather, it just always returns zero rows, no matter what.

显然，它没有做我天真以为的那样 - 相反，它总是返回零行，无论如何。

It doesn't really matter much to me whether the resulting set gives me one row for each package deal that should apply, or one for each package deal that shouldn't apply—but how do I get it to show me either in a single query?

对于我来说，对于每个应该适用的包裹交易，结果集合给我一行，或者对于每个不应用的包裹交易，给我一个并不重要 - 但是如何让它在单个中显示给我查询？

Is it even possible?

它甚至可能吗？

1 个解决方案

#1

The problem with your query above is that sale_id is also NULL in the missing row that you're interested in, due to the LEFT JOIN.

上面的查询问题是，由于LEFT JOIN，sale_id在您感兴趣的缺失行中也为NULL。

This query will return the deal_id for any deals that DO NOT apply to a given order:

对于不适用于给定订单的任何交易，此查询将返回deal_id：

SELECT DISTINCT
            pd.deal_id
FROM        package_deals pd
JOIN        deals_items di on pd.deal_id = di.deal_id
WHERE       di.item_id NOT IN (SELECT item_id FROM purchases WHERE sale_id = 3092)

From that it's easy to work out the ones that do apply. Note that for a fully functioning system, you'd still need to take the purchase quantities into account - e.g. if the customer had bought 2 of two the items in the deal, but only 1 of the third... etc.

从中可以轻松找出适用的那些。请注意，对于功能完备的系统，您仍需要考虑购买数量 - 例如如果客户购买了交易中的两个中的两个，但只有第三个中的一个......等等。

A SQL fiddle demonstrating the query is here: http://sqlfiddle.com/#!9/f2ae4/8

演示查询的SQL小提琴在这里：http：//sqlfiddle.com/#！9 / f2ae4 / 8

Note that I've made my joins using the ON syntax, as I'm simply more familiar than with USING. I expect that would work too if you prefer it.

请注意，我使用ON语法进行了连接，因为我比使用USING更熟悉。如果您愿意，我希望这也会奏效。

#1