基于条件的SQL行拆分：将超量ItemCount拆分为多行

需求说明 #

• 数据表中每条记录的ItemCount字段值若超过10，需拆分为多条记录：
  • 前N条记录的ItemCount取10（N为ItemCount // 10）
  • 最后一条记录的ItemCount取剩余值（ItemCount % 10，若原数值为10的倍数，则所有拆分行的ItemCount均为10）
• 拆分后，除ItemCount和InvoiceNumber外，其余字段（Account、Credit、Debt、Net）保持与原记录一致
• InvoiceNumber按拆分后的行顺序，依次使用10、20、30循环赋值

原始数据表 #

期望输出表 #

实现方案 #

核心思路 #

通过生成连续数字序列模拟拆分行数，关联原表后计算每条拆分记录的ItemCount和InvoiceNumber，实现自动化拆分。

SQL代码（MySQL为例） #

WITH RECURSIVE nums AS (
    -- 生成连续数字序列，覆盖最大可能的拆分行数（此处设为100，可按需调整）
    SELECT 1 AS num
    UNION ALL
    SELECT num + 1 FROM nums WHERE num < 100
),
split_records AS (
    SELECT 
        t.Account,
        t.Credit,
        t.Debt,
        t.Net,
        -- 计算当前拆分行的ItemCount
        CASE
            -- 非最后一行取10
            WHEN nums.num < CEIL(t.ItemCount / 10) THEN 10
            -- 最后一行：若原数值是10的倍数则取10，否则取余数
            ELSE IF(t.ItemCount % 10 = 0, 10, t.ItemCount % 10)
        END AS ItemCount,
        -- 生成10、20、30循环的InvoiceNumber
        ((nums.num - 1) % 3) * 10 + 10 AS InvoiceNumber
    FROM your_table t
    -- 关联数字表，确定每条原记录的拆分行数
    JOIN nums ON nums.num <= CEIL(t.ItemCount / 10)
    -- 过滤原数值为10的倍数时的多余行
    WHERE NOT (t.ItemCount % 10 = 0 AND nums.num > t.ItemCount / 10)
)
SELECT * FROM split_records ORDER BY Account, Credit, InvoiceNumber;

代码说明 #

1. 递归CTE nums：生成连续数字，用于对应拆分后的每一行，数值范围可根据业务中最大的ItemCount调整。
2. 关联逻辑：通过nums.num <= CEIL(t.ItemCount / 10)计算每条原记录需要拆分的总行数。
3. ItemCount计算：区分非最后一行和最后一行的取值规则，同时处理10的倍数的特殊情况。
4. InvoiceNumber生成：通过取模运算实现10、20、30的循环赋值，若需要其他编号规则可修改该公式。
5. 排序：确保输出结果与示例顺序一致。

其他数据库适配 #

• PostgreSQL/SQL Server：递归CTE写法基本一致，无需核心逻辑调整。
• Oracle：可使用CONNECT BY生成数字序列替代递归CTE，示例：

  SELECT LEVEL AS num FROM dual CONNECT BY LEVEL <= 100
  

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