如何将MySQL中一行库存数据按Qtd值拆分为多行？

我们有一张库存数据表，通过product_code字段区分不同产品，Qtd字段存储对应产品的库存数量。需要将单条产品记录拆分为Qtd数值对应的行数，每行的Qtd固定为1，示例如下：

原表：

拆分后目标表：

拆分的核心需求是为每个产品分配唯一序列号（序列号存储在无法进行关联查询的外部数据库），此前通过软件拆分仅能匹配第一个序列号，其余无法正确分配，拆分记录可彻底解决该匹配问题。

不同数据库的具体实现方案 #

MySQL/MariaDB（8.0+版本） #

借助递归CTE生成数字序列，再关联原表完成拆分：

WITH RECURSIVE numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < (SELECT MAX(Qtd) FROM inventory)
)
SELECT 
    ROW_NUMBER() OVER(ORDER BY i.product_code, n) AS 行,
    i.product_code,
    1 AS Qtd
FROM inventory i
JOIN numbers n ON n.n <= i.Qtd
ORDER BY i.product_code, n;

SQL Server #

使用递归CTE生成序列，注意解除递归层数限制：

WITH numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < (SELECT MAX(Qtd) FROM inventory)
)
SELECT 
    ROW_NUMBER() OVER(ORDER BY i.product_code, n) AS 行,
    i.product_code,
    1 AS Qtd
FROM inventory i
JOIN numbers n ON n.n <= i.Qtd
ORDER BY i.product_code, n
OPTION (MAXRECURSION 0); -- 当Qtd数值较大时必须添加此选项

PostgreSQL #

利用内置的generate_series函数实现更简洁的拆分：

SELECT 
    ROW_NUMBER() OVER(ORDER BY i.product_code, s) AS 行,
    i.product_code,
    1 AS Qtd
FROM inventory i
CROSS JOIN generate_series(1, i.Qtd) s
ORDER BY i.product_code, s;

Oracle #

通过CONNECT BY层级查询完成拆分：

SELECT 
    ROW_NUMBER() OVER(ORDER BY product_code, LEVEL) AS 行,
    product_code,
    1 AS Qtd
FROM inventory
CONNECT BY LEVEL <= Qtd
    AND PRIOR product_code = product_code
    AND PRIOR SYS_GUID() IS NOT NULL; -- 防止出现重复记录

注意事项 #

• 如果库存表中Qtd的最大值很大，递归CTE可能存在性能瓶颈，建议提前创建一个包含足够多连续数字的辅助表替代递归生成序列
• 拆分后的数据可以直接插入新表，只需在SELECT语句末尾添加INTO 新表名（不同数据库语法略有差异，需对应调整）
• 确保原表中product_code和Qtd字段的数据类型正确，避免因类型不兼容导致的拆分错误

内容的提问来源于stack exchange，提问作者Pedro Henrique Trentin