如何实现Machine与Inventory表的无重复库存一对一匹配？

问题场景 #

有两张业务表：

• Machine：记录每台机器上次运行时使用的库存属性（如颜色、尺寸）
• Inventory：记录未被机器使用过的新库存ID及其属性

已经通过CROSS JOIN结合颜色、尺寸筛选出了所有机器可匹配的库存组合，但需要实现一台机器对应唯一库存、库存不重复分配的逻辑，之前用循环遍历的方式效率太低，需要纯SQL的高效解决方案。

解决方案：基于窗口函数的排序匹配 #

核心思路是给机器和对应属性组的库存分别分配排序序号，通过序号一一匹配，实现无重复的一对一分配，完全替代循环逻辑。

示例SQL（适配常见关系型数据库） #

假设表结构如下：

• Machine：machine_id（机器ID）、last_used_color（上次用的颜色）、last_used_size（上次用的尺寸）
• Inventory：inventory_id（库存ID）、color（颜色）、size（尺寸）

WITH machine_ranks AS (
    -- 给每台机器按ID排序分配唯一序号
    SELECT
        machine_id,
        last_used_color,
        last_used_size,
        ROW_NUMBER() OVER (ORDER BY machine_id) AS rank_num
    FROM Machine
),
inventory_ranks AS (
    -- 按颜色+尺寸分组，给每组内的库存分配序号
    SELECT
        inventory_id,
        color,
        size,
        ROW_NUMBER() OVER (PARTITION BY color, size ORDER BY inventory_id) AS rank_num
    FROM Inventory
)
-- 通过属性匹配+序号匹配实现唯一分配
SELECT
    m.machine_id,
    i.inventory_id
FROM machine_ranks m
LEFT JOIN inventory_ranks i
    ON m.last_used_color = i.color
    AND m.last_used_size = i.size
    AND m.rank_num = i.rank_num
-- 可选：过滤掉无匹配库存的机器
WHERE i.inventory_id IS NOT NULL;

逻辑说明 #

1. machine_ranks：给所有机器分配唯一的排序序号，确保每台机器有一个专属标识
2. inventory_ranks：将库存按颜色+尺寸分组，每组内的库存按ID排序并分配序号，同一属性组的库存序号连续递增
3. 最后通过属性匹配+序号匹配，让每台机器拿到对应属性组内的唯一库存，既保证匹配逻辑正确，又避免库存重复分配

扩展处理 #

• 如果某属性组的库存数量少于对应机器数量：未匹配到库存的机器会在结果中显示NULL，可根据业务需求标记为"无可用库存"
• 如果库存数量多于机器数量：只会分配前N个库存（N为对应属性组的机器数），剩余库存可留作后续分配

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