mysql - 如何编写一个 sql 查询返回可以用给定项目完成的订单列表

Question

我是 SQL 的新手，我需要编写一个查询来返回可以用库存中的商品履行的订单列表。

假设下表显示了订单和完成订单所需的元素。

------------------------------
| OrderID | Required Item No |
------------------------------
| ORD001  |         Item007  |
| ORD001  |         Item008  |
| ORD001  |         Item009  |
| ORD002  |         Item008  |
| ORD002  |         Item009  |
| ORD002  |         Item012  |
| ORD003  |         Item008  |
| ORD003  |         Item014  |

所以为了得到满足，

• ORD001 需要 Item007、Item008、Item009
• ORD002 需要 Item008、Item009、Item012
• ORD003 需要 Item008,Item014

假设这张表显示了我的库存

--------------------------
| Item Unique Id |Item No |
--------------------------
| 001            | Item007|
| 002            | Item008|
| 003            | Item008|
| 004            | Item012|
| 005            | Item009|
| 006            | Item014|
| 007            | Item015|

在这种情况下，查询应返回 ORD001 和 ORD003。有人可以帮我解决这个问题吗？

我的第一次尝试

以下查询返回我的库存

SELECT ItemNo, COUNT(ItemNo) AS QTY 
FROM InventoryTable 
GROUP BY ItemNo

------------------------------
| ItemNo |        QTY        |
------------------------------
| Item007|         1         |
| Item008|         2         |
| Item009|         1         |
| Item012|         1         |
| Item014|         1         | 
| Item015|         1         |

此查询返回特定订单所需项目的列表

SELECT ItemNo, COUNT(ItemNo) AS QTY 
FROM OrdersTable 
Where OrderID ='ORD003' 
Group By ReqItemNo

------------------------------
| ItemNo  |        QTY        |
------------------------------
| Item008 |         1         |
| Item0014|         1         |

我找不到一种方法来减去这两个表之间的项目数量，并为每个订单进行减法并返回可用订单。

感谢@Gordon Linoff 的建议

select t1.OrderID
from t1 
left join inventory i
    on t.required_item_no = i.item_no
group by t1.OrderId
having count(*) = count(i.item_no)

此查询似乎是正确的，但对于上面的示例，它返回 ORD001,ORD002,ORD003。当 Item009 分配给 ORD001 时，因为将不再有 Item009，所以查询不应该满足 ORD002。

Answer 1

基本上，您需要订单所需商品在库存中的订单。这将提供该列表，但它会忽略其他订单要求。 IOW，如果第一个订单耗尽了第二个订单所需的项目，它不会接受那个。

SELECT 
    t1.OrderID,
    COUNT(DISTINCT t1.required_item_no) as `required_items`,
    COUNT(DISTINCT i.item_no) as `items_on_hand`
FROM t1 
LEFT JOIN inventory i
    ON t1.required_item_no = i.item_no
GROUP BY t1.OrderId
HAVING `required_items` = `items_on_hand`;

更新

我看到上面的查询没有考虑每个库存项目的数量，并且会包括数量为零的项目。以下更新应解决此问题。

由于订单商品和库存的存储方式，我没有直接引用表格，而是在下面使用了子选择。当向表中添加大量项目时，性能将成为一个问题。您可能需要重新考虑数量的存储方式，并为每个库存项目留一行，并用一列存储现有数量。订单表也是如此。

SELECT 
    t1.OrderID,
    COUNT(DISTINCT t1.required_item_no) as `required_items`,
    COUNT(DISTINCT i.item_no) as `items_on_hand`
FROM (
        SELECT 
            OrderID,
            required_item_no,
            COUNT(*) as `QTY`
        FROM OrdersTable
        GROUP BY OrderID, required_item_no
        ) t1 

LEFT JOIN (
            SELECT
                item_no,
                COUNT(*) as `QTY`
            FROM inventory
            GROUP BY item_no
        ) i
    ON t1.required_item_no = i.item_no AND 
    i.QTY >= t1.QTY
GROUP BY t1.OrderId
HAVING `required_items` = `items_on_hand`;