mysql - 使用 MySQL 选择与此订单具有完全相同项目的所有订单

Question

我有点被这个难住了。我想要做的是获取与给定订单具有完全相同的项目和数量的所有订单。

例如，订单号 100 有 1 个蓝色小部件、2 个粉红色小部件和 1 个绿色小部件。我想从数据库中选择所有也恰好有 1 个蓝色小部件、2 个粉红色小部件和 1 个绿色小部件的订单，并且没有比这更多或更少的项目。

目标是回答以下问题:“上次运送这些元素的确切组合时，我们将其装在哪个箱子里运送？”。如果我能得到在元素方面完全匹配的订单，我就能看到之前使用的是什么盒子。

我的表结构是:

Orders_Items

+---------------------------+-----------+-------------+
| OrderID   | OrderPartID   |   ItemID  | QtyOrdered  |
+---------------------------+-----------+-------------+
| 1         |   21          |      4    | 11.5000     |
| 2         |    8          |      5    |  4.3333     |
+---------------------------+-----------+-------------+

Orders_OrderParts

+--------------+------------+-----------+
| OrderPartID  | OrderID    |  StatusID |
+--------------+------------+-----------+
| 1            |   21       |    4      |
| 2            |    8       |    5      |
+---------------------------+-----------+

Orders_OrderParts_2_Shipments

+--------------+------------+--------------+
| OrderPartID  | OrderID    |  ShipmentID  |
+--------------+------------+--------------+
| 1            |   21       |      4       |
| 2            |    8       |      5       |
+---------------------------+--------------+

“ShipmentID”是我们试图找到的神奇数字，因为它会告诉我过去使用过哪些箱子。

我以此为基础开始的。

SELECT *, COUNT(*) AS ItemsInOrder
FROM Orders_Items OI
LEFT JOIN Orders_OrderParts OP
   ON OP.OrderPartID = OI.OrderPartID
LEFT JOIN Orders_OrderParts_2_Shipments OP2S
   ON OP2S.OrderPartID = OP.OrderPartID
WHERE OP.StatusID=800 # Only select Orders that have shipped.
GROUP BY OI.OrderPartID

这是一个有效的查询，但目前它只返回按订单部分分组的所有项目。我需要删除所有不包含当前订单部分中的项目的订单部分。

查询:

SELECT * FROM `Orders_Items` WHERE OrderPartID = 100

返回当前订单部分中的所有商品及其数量，即:

+--------------+------------+--------------+
| OrderPartID  |   ItemID   |  Quantity    |
+--------------+------------+--------------+
| 100          |     21     |      1       |
| 100          |     8      |      3       |
+---------------------------+--------------+

所以我需要缩小第一个查询的范围，只显示恰好有项目 #21 中的 1 个和项目 #8 中恰好有 3 个的订单部分。

这就是我被困的地方。我可以分别执行这两个查询并使用 PHP 遍历结果并比较它们，但我很好奇使用纯 MySQL 解决方案是否更有效......或者在这种情况下是否甚至可以进行单个查询？

Answer 1

我觉得我有。我将解释我首先想到的过程和测试设置...

---

设置

我创建了三张表，一张用于订单，一张用于元素，一张用于连接这两个表:

CREATE TABLE Items (
    ItemId int NOT NULL,
    ItemName nvarchar(50) NULL
);

CREATE TABLE Orders (
    OrderId int NOT NULL,
    OrderName nvarchar(50) NULL,
    BoxType nvarchar(50) NULL
);

CREATE TABLE OrdersItems(
    OrderId int NOT NULL,
    ItemId int NOT NULL,
    Quantity int  NOT NULL
);

我插入了一些测试订单和项目:

INSERT INTO Items (ItemId, ItemName) VALUES (1, 'Apples'),(2, 'Oranges'),(3, 'Pears');
INSERT INTO Orders (OrderId, OrderName, BoxType) VALUES
    (1, 'Order1', 'small'),
    (2, 'Order2', 'medium'),
    (3, 'Order3', 'small'),
    (4, 'Order4', 'large'),
    (5, 'Order5', 'small');
INSERT INTO OrdersItems (OrderId, ItemId, Quantity) VALUES (1, 1, 10),(1, 2, 5);
INSERT INTO OrdersItems (OrderId, ItemId, Quantity) VALUES (2, 1, 5),(2, 2, 7),(2, 3, 2);
INSERT INTO OrdersItems (OrderId, ItemId, Quantity) VALUES (3, 1, 10),(3, 2, 5);
INSERT INTO OrdersItems (OrderId, ItemId, Quantity) VALUES (4, 1, 10),(4, 2, 5),(4, 3, 2);
INSERT INTO OrdersItems (OrderId, ItemId, Quantity) VALUES (5, 1, 4),(5, 3, 1);

这给出了这组订单:

---

订单汇总

所以这一切所做的就是创建 5 个订单:

1. 10个苹果和5个橙子(目标顺序)
2. 5 个苹果、7 个橙子和 2 个梨(有苹果和橙子，但数量不对，还有梨)
3. 10个苹果和5个橙子(匹配顺序)
4. 10 个苹果、5 个橙子和 2 个梨(正确的苹果和橙子，还有梨)
5. 4 个苹果和 1 个梨(没有橙子)

---

获取数据

首先，我们得到一个列表，其中包含至少与我们的目标订单具有相同商品的所有订单。所以在我们的例子中它必须有 10 个苹果和 5 个橙子，但可以也有额外的订单项目:

SELECT DISTINCT O2.OrderName
FROM Orders O1
LEFT JOIN OrdersItems OI1 ON (O1.OrderId=OI1.OrderId)
LEFT JOIN Items I1 ON (OI1.ItemId=I1.ItemId)
LEFT JOIN OrdersItems OI2 ON (OI1.ItemId=OI2.ItemId AND OI1.OrderId<>OI2.OrderId AND OI1.Quantity=OI2.Quantity)
LEFT JOIN Orders O2 ON (OI2.OrderId=O2.OrderId)
WHERE O1.OrderId=1;

接下来我们得到一个订单列表，其中包含目标订单不的商品:

SELECT DISTINCT O1.OrderName
FROM Orders O1
LEFT JOIN OrdersItems OI1 ON (O1.OrderId=OI1.OrderId)
CROSS JOIN Orders O2
LEFT JOIN OrdersItems OI2 ON (O2.OrderId=OI2.OrderId AND OI1.ItemId=OI2.ItemId and OI1.Quantity=OI2.Quantity)
WHERE O1.OrderId<>1
AND O2.OrderId=1
AND OI2.ItemId IS NULL;

现在将两者结合起来只得到相同的订单!

SELECT DISTINCT O2.OrderName, O2.BoxType
FROM Orders O1
LEFT JOIN OrdersItems OI1 ON (O1.OrderId=OI1.OrderId)
LEFT JOIN Items I1 ON (OI1.ItemId=I1.ItemId)
LEFT JOIN OrdersItems OI2 ON (OI1.ItemId=OI2.ItemId AND OI1.OrderId<>OI2.OrderId AND OI1.Quantity=OI2.Quantity)
LEFT JOIN Orders O2 ON (OI2.OrderId=O2.OrderId)
WHERE O1.OrderId=1
AND O2.OrderId NOT IN
(
    SELECT DISTINCT O1.OrderId
    FROM Orders O1
    LEFT JOIN OrdersItems OI1 ON (O1.OrderId=OI1.OrderId)
    CROSS JOIN Orders O2
    LEFT JOIN OrdersItems OI2 ON (O2.OrderId=OI2.OrderId AND OI1.ItemId=OI2.ItemId and OI1.Quantity=OI2.Quantity)
    WHERE O1.OrderId<>1
    AND O2.OrderId=1
    AND OI2.ItemId IS NULL
);

现在，如果有多个订单相同但使用了不同的盒子，您可能会遇到问题，在这种情况下，您必须要么显示所有订单，要么选择 COUNT(OrderId) 最大的订单或其他任何订单。

---

我用来测试这个的完整脚本(适用于 MSSQL 和 MySQL)可以是 found here ，它还有几个额外的测试订单。