Oracle中如何根据两列数值差生成多行数据？

针对百万级数据量的拆分需求，核心是避免逐行循环（如游标），利用数据库的集合式操作或预生成数字表来提升效率，同时保留前导零格式。以下是主流数据库的实现方案：

原始数据表 #

期望输出表 #

1. MySQL 方案 #

思路 #

利用递归CTE生成区间内的所有数值，再结合原始表关联，最后通过LPAD函数补全前导零。MySQL 8.0+支持递归CTE，低版本可提前生成数字辅助表。

代码 #

WITH RECURSIVE num_range AS (
    SELECT 
        ACCOUNT_NUMBER,
        CAST(START_NUMBER AS UNSIGNED) AS current_num,
        CAST(END_NUMBER AS UNSIGNED) AS end_num,
        LENGTH(START_NUMBER) AS num_length
    FROM your_table
    UNION ALL
    SELECT 
        ACCOUNT_NUMBER,
        current_num + 1,
        end_num,
        num_length
    FROM num_range
    WHERE current_num < end_num
)
SELECT 
    ACCOUNT_NUMBER,
    LPAD(current_num, num_length, '0') AS START_NUMBER,
    NULL AS END_NUMBER
FROM num_range
ORDER BY ACCOUNT_NUMBER, current_num;

优化点 #

若数据量极大（超千万），提前创建包含足够多连续数字的辅助表（如numbers表，存储1到10000000的数字），用JOIN替代递归CTE，性能更稳定：

SELECT 
    t.ACCOUNT_NUMBER,
    LPAD(n.num, LENGTH(t.START_NUMBER), '0') AS START_NUMBER,
    NULL AS END_NUMBER
FROM your_table t
JOIN numbers n 
    ON n.num BETWEEN CAST(t.START_NUMBER AS UNSIGNED) AND CAST(t.END_NUMBER AS UNSIGNED)
ORDER BY t.ACCOUNT_NUMBER, n.num;

2. PostgreSQL 方案 #

思路 #

使用generate_series函数直接生成区间数值，结合lpad处理前导零，集合式操作效率极高，适合百万级数据。

代码 #

SELECT 
    t.ACCOUNT_NUMBER,
    LPAD(gs.num::TEXT, LENGTH(t.START_NUMBER), '0') AS START_NUMBER,
    NULL::TEXT AS END_NUMBER
FROM your_table t
CROSS JOIN generate_series(
    CAST(t.START_NUMBER AS INTEGER),
    CAST(t.END_NUMBER AS INTEGER)
) AS gs(num)
ORDER BY t.ACCOUNT_NUMBER, gs.num;

注意事项 #

若START_NUMBER是固定长度的字符串（如5位），可直接指定长度（LPAD(gs.num::TEXT, 5, '0')），避免重复计算LENGTH提升性能。

3. SQL Server 方案 #

思路 #

利用递归CTE或master..spt_values系统表（适用于小范围区间），大范围区间推荐递归CTE或自定义数字辅助表，同时用RIGHT函数补前导零。

代码（递归CTE版） #

WITH num_range AS (
    SELECT 
        ACCOUNT_NUMBER,
        CAST(START_NUMBER AS INT) AS current_num,
        CAST(END_NUMBER AS INT) AS end_num,
        LEN(START_NUMBER) AS num_length
    FROM your_table
    UNION ALL
    SELECT 
        ACCOUNT_NUMBER,
        current_num + 1,
        end_num,
        num_length
    FROM num_range
    WHERE current_num < end_num
)
SELECT 
    ACCOUNT_NUMBER,
    RIGHT('00000' + CAST(current_num AS VARCHAR), num_length) AS START_NUMBER, -- 根据实际长度调整前置零数量
    NULL AS END_NUMBER
FROM num_range
ORDER BY ACCOUNT_NUMBER, current_num
OPTION (MAXRECURSION 0); -- 解除递归深度限制，适用于大范围区间

优化点 #

百万级数据下，提前创建数字辅助表（如Numbers表），用JOIN替代递归CTE，减少递归开销：

SELECT 
    t.ACCOUNT_NUMBER,
    RIGHT('00000' + CAST(n.Number AS VARCHAR), LEN(t.START_NUMBER)) AS START_NUMBER,
    NULL AS END_NUMBER
FROM your_table t
JOIN Numbers n 
    ON n.Number BETWEEN CAST(t.START_NUMBER AS INT) AND CAST(t.END_NUMBER AS INT)
ORDER BY t.ACCOUNT_NUMBER, n.Number;

通用性能建议 #

• 确保START_NUMBER和END_NUMBER列建立索引，提升区间关联速度。
• 拆分操作尽量在数据库端完成，避免导出到应用层处理，减少IO开销。
• 若拆分后数据量超千万，可分批次处理（如按ACCOUNT_NUMBER分段），避免一次性生成过大结果集。

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