如何通过JOIN表实现订单与库存的唯一匹配SQL查询？

这个问题的核心是要实现同商品的订单与库存条目一一匹配，且每个库存仅被使用一次——普通的INNER JOIN会产生笛卡尔积（比如item_id=2的订单有2条、库存有3条，直接关联会得到6条冗余记录），显然满足不了你的需求。

解决方案思路 #

我们可以利用窗口函数ROW_NUMBER()，给同一商品的订单和库存分别按顺序编号，再通过「商品ID+编号」的组合进行关联，这样就能保证每个订单匹配唯一的库存条目，且库存不会被重复使用。

具体SQL语句 #

WITH numbered_orders AS (
    SELECT 
        id AS order_id,
        item_id,
        -- 按item_id分组，给每个订单按id排序生成行号
        ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY id) AS order_row
    FROM orders
),
numbered_inventory AS (
    SELECT 
        id AS inventory_id,
        item_id,
        -- 按item_id分组，给每个库存按id排序生成行号
        ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY id) AS inv_row
    FROM inventory
)
SELECT 
    no.order_id,
    no.item_id,
    ni.inventory_id
FROM numbered_orders no
INNER JOIN numbered_inventory ni 
    ON no.item_id = ni.item_id 
    AND no.order_row = ni.inv_row;

逻辑解释 #

1. numbered_orders子查询：对orders表按item_id分组，给每组内的订单按id排序并生成递增行号。比如item_id=2的两个订单，行号分别为1和2。
2. numbered_inventory子查询：对inventory表做同样的处理，按item_id分组后给库存条目生成行号。比如item_id=2的三个库存，行号分别为1、2、3。
3. 最终关联：通过item_id匹配商品，同时要求订单的行号和库存的行号相等。这样每个订单只会对应同商品下相同行号的库存，多余的库存（比如item_id=2的第3条库存）因为没有对应行号的订单，会被自动过滤；而没有库存的订单（比如item_id=4的订单）也会因为INNER JOIN被排除。

执行这条SQL后，得到的结果完全符合你期望的输出：

+----------+---------+--------------+
| order_id | item_id | inventory_id |
+----------+---------+--------------+
| 1        | 2       | 3            |
| 2        | 2       | 4            |
| 3        | 3       | 1            |
| 4        | 3       | 2            |
+----------+---------+--------------+

内容的提问来源于stack exchange，提问作者Frank Malenfant