如何将含数值范围的表行拆分为每行对应单个数值？

现有如下结构的表（假设表名为range_status）：

需要通过高效的SELECT语句得到如下展开后的结果：

不同数据库有对应的原生高效实现方式，以下是主流数据库的解决方案：

1. PostgreSQL #

利用原生的generate_series函数直接生成区间序列，性能最优：

SELECT generate_series(r."from", r."to") AS serial, r.status
FROM range_status r;

2. MySQL 8.0+ / MariaDB 10.2+ #

使用递归CTE（公共表表达式）生成序列，无需临时表，适合任意区间范围：

WITH RECURSIVE serial_cte AS (
    SELECT "from" AS serial, "to", status
    FROM range_status
    UNION ALL
    SELECT serial + 1, "to", status
    FROM serial_cte
    WHERE serial < "to"
)
SELECT serial, status
FROM serial_cte
ORDER BY serial;

3. SQL Server #

递归CTE方式（通用型）：

WITH serial_cte AS (
    SELECT [from] AS serial, [to], status
    FROM range_status
    UNION ALL
    SELECT serial + 1, [to], status
    FROM serial_cte
    WHERE serial < [to]
)
SELECT serial, status
FROM serial_cte
ORDER BY serial
OPTION (MAXRECURSION 0); -- 区间范围超过100时需添加，取消递归次数限制

系统表关联方式（适合小范围区间）：

SELECT 
    r.[from] + v.number AS serial,
    r.status
FROM range_status r
JOIN master..spt_values v 
    ON v.type = 'P' 
    AND v.number BETWEEN 0 AND (r.[to] - r.[from])
ORDER BY serial;

4. Oracle #

CONNECT BY层级查询方式：

SELECT 
    r."from" + LEVEL - 1 AS serial,
    r.status
FROM range_status r
CONNECT BY LEVEL <= r."to" - r."from" + 1
    AND PRIOR r."from" = r."from"
    AND PRIOR SYS_GUID() IS NOT NULL; -- 避免跨行递归

递归CTE方式：

WITH serial_cte AS (
    SELECT "from" AS serial, "to", status
    FROM range_status
    UNION ALL
    SELECT serial + 1, "to", status
    FROM serial_cte
    WHERE serial < "to"
)
SELECT serial, status
FROM serial_cte
ORDER BY serial;

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