SQL技术问询：基于库存数量限制货位推荐次数的关联查询问题

我明白你的需求了——要给每个客户的物料需求分配货位，每个货位最多能被推荐的次数等于它的库存数量，库存用完就不能再用这个货位了。咱们一步步来解决这个问题。

先明确你的测试数据 #

customers表 #

inventory表 #

期望输出 #

现有SQL的问题 #

你当前的SQL逻辑没有正确生成每个货位可分配的「次数区间」，导致关联条件无法准确匹配到对应的货位。咱们换个更直观的思路：先把每个货位按照库存数量拆分成对应次数的行（比如A84库存2，就拆成2行，每行对应一次推荐机会），然后给这些拆分后的行按物料分组编号；同时给客户的需求按物料分组编号，最后通过编号匹配来分配货位。

正确的SQL实现 #

这里以支持CTE的SQL环境为例（比如MySQL 8+、PostgreSQL、SQL Server等）：

WITH inventory_expanded AS (
    -- 把每个货位按库存数拆分成多行，每行对应一次可推荐的机会
    SELECT 
        item,
        bin,
        ROW_NUMBER() OVER (PARTITION BY item ORDER BY bin) AS allocate_seq
    FROM inventory
    JOIN (
        -- 生成数字序列，用来拆分库存行，这里假设最大库存不超过100，可根据实际调整
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
        SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
        SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    ) nums ON nums.n <= inventory.qty
),
customer_demand AS (
    -- 给每个客户的物料需求按物料分组编号，用来匹配货位的分配序列
    SELECT 
        customer,
        item,
        ROW_NUMBER() OVER (PARTITION BY item ORDER BY customer) AS demand_seq
    FROM customers
)
SELECT 
    cd.customer,
    cd.item,
    COALESCE(ie.bin, '（无剩余货位）') AS bin
FROM customer_demand cd
LEFT JOIN inventory_expanded ie 
    ON cd.item = ie.item 
    AND cd.demand_seq = ie.allocate_seq
ORDER BY cd.customer, cd.item;

逻辑解释 #

1. inventory_expanded：通过数字表和inventory关联，把每个货位拆成qty行，然后给每个物料下的货位分配序列号（比如Item1的A1是1，A84是2、3，C32是4、5，D01是6）。
2. customer_demand：给每个物料下的客户需求按客户顺序分配序列号（比如Item1的需求序列是1到6，对应6个客户的需求）。
3. 最后关联两个CTE，通过物料和序列号匹配，匹配不到的就显示「（无剩余货位）」，完美符合你的期望输出。

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