PostgreSQL的generate_series函数对应的Oracle SQL等效实现咨询

原场景说明 #

你需要迁移的PostgreSQL SQL通过generate_series()函数，根据每条记录的QTY值生成对应数量的重复行：

SELECT 
     ITEM,
     generate_series(1, ITEM.QTY::INTEGER) AS TABLE_ID
FROM TABLE_NAME

（注意：TABLE是Oracle关键字，建议重命名表为TABLE_NAME避免语法冲突）

原始数据示例：

执行后预期输出：

Oracle的几种等效实现 #

1. 用CONNECT BY层级查询（推荐）

这是Oracle中实现此类需求最常用的方式，利用LEVEL伪列生成序列：

SELECT 
    t.ITEM,
    LEVEL AS TABLE_ID
FROM TABLE_NAME t
CONNECT BY LEVEL <= t.QTY
    AND PRIOR t.ITEM = t.ITEM
    AND PRIOR SYS_GUID() IS NOT NULL

• LEVEL会生成从1开始的连续整数，直到达到QTY指定的数量
• PRIOR t.ITEM = t.ITEM确保每条原始记录独立生成自己的序列
• PRIOR SYS_GUID() IS NOT NULL防止多条记录时出现不必要的笛卡尔积（通过生成唯一值保证层级查询的独立性）

2. 递归CTE（Oracle 11gR2+支持）

如果偏好递归语法，可使用递归公共表达式，逻辑和PostgreSQL的generate_series()更贴近：

WITH series AS (
    SELECT 
        ITEM,
        QTY,
        1 AS TABLE_ID
    FROM TABLE_NAME
    UNION ALL
    SELECT 
        s.ITEM,
        s.QTY,
        s.TABLE_ID + 1
    FROM series s
    WHERE s.TABLE_ID + 1 <= s.QTY
)
SELECT ITEM, TABLE_ID
FROM series
ORDER BY ITEM, TABLE_ID;

3. 数字辅助表关联（适合大数据量）

如果有预先创建的数字辅助表（比如存储了1到最大可能QTY值的表），可以用关联查询实现：

-- 假设存在辅助表NUMBERS，包含列N（值从1开始连续）
SELECT 
    t.ITEM,
    n.N AS TABLE_ID
FROM TABLE_NAME t
JOIN NUMBERS n ON n.N <= t.QTY
ORDER BY t.ITEM, n.N;

这种方式在数据量较大时性能更优，但需要提前维护辅助表的完整性。

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