如何将超合规长度金额的SQL单条记录拆分为多条合规记录

对接的客户系统对TOT_AMT字段有10字符长度限制，最大合规值为9999999.99，超过该值的记录会被拒收。需求是将单条大额记录动态拆分为多条，每条金额不超过合规值，且支持任意金额输入。

你尝试用LAG函数实现的思路存在问题：LAG仅能访问结果集中已存在的前一行数据，无法动态生成新的行，因此只能返回单条记录，无法完成拆分需求。

以下是两种可行的实现方案：

方法一：递归CTE实现拆分 #

递归CTE可以循环生成所需的拆分记录，直到剩余金额为0，适用于Oracle、PostgreSQL、SQL Server等支持递归语法的数据库。

WITH RECURSIVE_SPLIT AS (
    -- 初始行：获取原始记录，计算第一条拆分金额与剩余金额
    SELECT 
        ACCT_NUM,
        TOT_AMT AS ORIGINAL_AMT,
        LEAST(TOT_AMT, 9999999.99) AS SPLIT_AMT,
        TOT_AMT - LEAST(TOT_AMT, 9999999.99) AS REMAINING_AMT
    FROM TABLE_NAME
    WHERE TOT_AMT > 9999999.99 -- 仅处理大额记录

    UNION ALL

    -- 递归生成后续拆分记录
    SELECT 
        ACCT_NUM,
        ORIGINAL_AMT,
        LEAST(REMAINING_AMT, 9999999.99) AS SPLIT_AMT,
        REMAINING_AMT - LEAST(REMAINING_AMT, 9999999.99) AS REMAINING_AMT
    FROM RECURSIVE_SPLIT
    WHERE REMAINING_AMT > 0 -- 剩余金额大于0时继续递归
)
-- 输出最终拆分结果
SELECT ACCT_NUM, SPLIT_AMT AS TOT_AMT
FROM RECURSIVE_SPLIT
ORDER BY ACCT_NUM;

逻辑说明 #

1. 初始查询提取原始大额记录，第一条拆分金额取原始金额与合规最大值的较小值，同时计算剩余未拆分金额。
2. 递归部分基于剩余金额持续生成新记录，直到剩余金额为0时终止递归。
3. 最终输出所有拆分后的记录，按账号排序。

方法二：利用数字序列生成拆分记录 #

如果数据库支持生成连续数字序列（如Oracle的CONNECT BY、PostgreSQL的generate_series），可先计算所需拆分行数，再通过数字序列关联生成拆分记录。

以Oracle为例：

WITH ORIGINAL_DATA AS (
    SELECT ACCT_NUM, TOT_AMT FROM TABLE_NAME WHERE TOT_AMT > 9999999.99
),
-- 计算每个记录的拆分总行数
SPLIT_INFO AS (
    SELECT 
        ACCT_NUM,
        TOT_AMT,
        CEIL(TOT_AMT / 9999999.99) AS TOTAL_ROWS -- 向上取整得到总行数
    FROM ORIGINAL_DATA
),
-- 生成覆盖最大行数的连续数字序列
NUM_SERIES AS (
    SELECT LEVEL AS ROW_NUM
    FROM DUAL
    CONNECT BY LEVEL <= (SELECT MAX(TOTAL_ROWS) FROM SPLIT_INFO)
)
-- 关联生成拆分记录
SELECT 
    SI.ACCT_NUM,
    CASE 
        WHEN NS.ROW_NUM < SI.TOTAL_ROWS THEN 9999999.99
        ELSE SI.TOT_AMT - (SI.TOTAL_ROWS - 1) * 9999999.99
    END AS TOT_AMT
FROM SPLIT_INFO SI
JOIN NUM_SERIES NS ON NS.ROW_NUM <= SI.TOTAL_ROWS
ORDER BY SI.ACCT_NUM, NS.ROW_NUM;

逻辑说明 #

1. 先筛选出原始大额记录，计算每个记录需要拆分的总行数（向上取整原始金额除以合规最大值）。
2. 生成足够覆盖所有记录最大行数的连续数字序列。
3. 关联原始记录与数字序列，前N-1行取合规最大值，最后一行取剩余金额。

两种方案对比：递归CTE通用性更强，无需预先计算最大行数；数字序列方案在数据库支持的场景下性能更优，适合处理大量记录。

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