如何分配数量从一个表到另一个查询?例如，选定的订单数量与实际订单数量相比较

Question

目前我正在开发一个订单管理系统，该系统接收订单并进行挑选。收到的订单数量总是为1 -例如，如果同一产品的订单中有两个项目，则会有两条线。这些存储在OrderItems表中。订单项按产品挑选，并存储在PickedItems表中，即

CREATE TABLE OrderItems
(
  OrderID int,
  Reference int,
  ProductCode varchar(20),
  Quantity int
);
CREATE TABLE PickedItems
(
  OrderID int,
  ProductCode varchar(20),
  Quantity int,
  Location varchar(20) DEFAULT NULL 
);

因此，例如，下面的插入创建了一个包含6个行项目的订单，其中3个用于同一产品，另外2个用于不同的产品，最后一个单独的行用于第三个产品。

INSERT INTO OrderItems VALUES (1, 1, 'BOOK', 1);
INSERT INTO OrderItems VALUES (1, 2, 'BOOK', 1);
INSERT INTO OrderItems VALUES (1, 3, 'BOOK', 1);
INSERT INTO OrderItems VALUES (1, 4, 'PEN', 1);
INSERT INTO OrderItems VALUES (1, 5, 'PEN', 1);
INSERT INTO OrderItems VALUES (1, 6, 'CHAIR', 1);

如果产品"BOOK"中有2个被选中，产品"CHAIR"中有1个被选中，那么以下代码将在PickedItems表中创建这些条目:

INSERT INTO PickedItems (OrderID, ProductCode, Quantity) VALUES (1, 'BOOK', 2);
INSERT INTO PickedItems (OrderID, ProductCode, Quantity) VALUES (1, 'CHAIR', 1);

当前使用以下查询来查找哪些Order Items已被成功挑选:

SELECT 
    OrderItems.Reference,
    OrderItems.ProductCode,
    OrderItems.Quantity,
    CASE
        WHEN SUM(OrderItemsCumulative.Quantity) <= PickedItems.Quantity THEN 1
        WHEN (SUM(OrderItemsCumulative.Quantity) - 1) < PickedItems.Quantity THEN PickedItems.Quantity - (SUM(OrderItemsCumulative.Quantity) - 1)
        ELSE 0
    END AS QuantityPicked
FROM
    OrderItems INNER JOIN
    OrderItems OrderItemsCumulative ON (OrderItems.OrderID = OrderItemsCumulative.OrderID) AND (OrderItems.ProductCode = OrderItemsCumulative.ProductCode) AND (OrderItems.Reference >= OrderItemsCumulative.Reference) LEFT JOIN
    PickedItems ON (OrderItems.OrderID = OrderItemsCumulative.OrderID) AND (OrderItems.ProductCode = PickedItems.ProductCode)
WHERE
    OrderItems.OrderID = 1
GROUP BY
    OrderItems.Reference,
    OrderItems.ProductCode,
    OrderItems.Quantity,
    PickedItems.Quantity

这一切都工作得很好，但我想扩展这一点，以便pickkeditems可以有多个位置与之关联(使用pickkeditems表中的位置列)，因此在pickkeditems表中可能有多个订单/产品条目，例如在"位置A"拾取的2本书和在"位置B"拾取的1本书，将有以下条目。

INSERT INTO PickedItems (OrderID, ProductCode, Quantity, Location) VALUES (1, 'BOOK', 2, 'Location A');
INSERT INTO PickedItems (OrderID, ProductCode, Quantity, Location) VALUES (1, 'BOOK', 1, 'Location B');

所以上面的查询没有考虑位置。

理想情况下，我希望在上述场景中返回以下内容，但我似乎无法使其适合查询。我是否最好使用存储过程重写查询并以个人为基础分配数据?

+-----------+-------------+-----------------+----------------+------------+
| Reference | ProductCode | QuantityOrdered | QuantityPicked |  Location  |
+-----------+-------------+-----------------+----------------+------------+
|         1 | Book        |               1 |              1 | Location A |
|         2 | Book        |               1 |              1 | Location A |
|         3 | Book        |               1 |              1 | Location B |
|         4 | Pen         |               1 |              0 | NULL       |
|         5 | Pen         |               1 |              0 | NULL       |
|         6 | Chair       |               1 |              0 | NULL       |
+-----------+-------------+-----------------+----------------+------------+

SQL小提琴

Answer 1

理想情况下，您应该调整模式以反映更改。

您可能需要通过向orderitems添加location或通过向pickitems添加引用来将order引用与位置关联起来，否则orderid/productid上总是存在笛卡尔坐标，因为它们在任何表中都不是唯一的。

使用存储过程，理论上可以绕过笛卡尔，但这只会掩盖数据模型的实际问题。