如何根据Shortage值限制SQL返回的location_id行数？

我编写了如下SQL代码：

SELECT t1.sku_id, t1.putaway_group, t1.shortage, t4.location_id, t4.qty_on_hand
FROM 
    (WHERE clauses)t1
   
LEFT JOIN
        (
        SELECT *
        FROM   (
          SELECT location_id, sku_id, qty_on_hand,
                DENSE_RANK() OVER ( PARTITION BY sku_id ORDER BY qty_on_hand DESC ) AS rnk
          FROM   inventory
          WHERE substr(zone_1,1,5) IN ('TOTEB','TOTEC')
        )
        WHERE  rnk = 1
        ORDER BY 2 DESC
        )t4
        ON t3.sku_id = t4.sku_id

当前输出中，我希望根据shortage的值返回对应数量的location_id行：比如当shortage为-84时，SKU为02295441的记录需要返回6行（6*16=96）来覆盖短缺量。目前我通过Excel Power Query实现了这个需求，但想知道能不能直接用SQL实现，该怎么编写逻辑来限制返回行数？

要实现按短缺量生成对应行数的需求，核心是生成数字序列关联原数据，从而复制对应次数的行，具体步骤如下：

1. 计算需复制的行数 #

先根据短缺量和单库存数量，算出每个SKU需要复制的行数：

• 取短缺量的绝对值ABS(t1.shortage)
• 用向上取整函数计算行数：CEIL(ABS(t1.shortage) / t4.qty_on_hand)，比如84/16=5.25，向上取整得到6行。

2. 生成数字序列（递归CTE） #

用递归公共表表达式生成足够大的数字序列，这里以1到1000为例（可根据实际需求调整上限）：

WITH num_seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM num_seq WHERE n < 1000
)

3. 关联生成最终结果 #

将数字序列和原查询结果关联，只保留序列数小于等于需复制行数的记录：

WITH num_seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM num_seq WHERE n < 1000
)
SELECT 
    t1.sku_id, 
    t1.putaway_group, 
    t1.shortage, 
    t4.location_id, 
    t4.qty_on_hand
FROM 
    (WHERE clauses)t1
LEFT JOIN
    (
    SELECT *
    FROM   (
      SELECT location_id, sku_id, qty_on_hand,
            DENSE_RANK() OVER ( PARTITION BY sku_id ORDER BY qty_on_hand DESC ) AS rnk
      FROM   inventory
      WHERE substr(zone_1,1,5) IN ('TOTEB','TOTEC')
    )
    WHERE  rnk = 1
    )t4 ON t1.sku_id = t4.sku_id -- 修正原代码中未定义的t3为t1
JOIN num_seq ON num_seq.n <= CEIL(ABS(t1.shortage) / t4.qty_on_hand)
ORDER BY t1.sku_id, num_seq.n;

注意事项 #

• 原SQL中ON t3.sku_id = t4.sku_id属于笔误，需改为t1.sku_id，因为t3未在查询中定义。
• 若数据库不支持递归CTE（如老版本MySQL），可改用数字表或笛卡尔积的方式生成序列。
• 按需调整num_seq的上限，确保能覆盖所有场景下的最大需要行数。

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