如何使用SQL根据关联表值将数字列表转换为连续序列表记录

需求说明 #

• 原始表记录值按顺序为3、3、4、1，需保留原始记录的先后顺序
• 每条记录对应数值N，需展开为N行，行值为1到N的连续整数
• 最终输出序列顺序为1、2、3、1、2、3、1、2、3、4、1

前置依赖 #

实现需要用到连续整数序列辅助表，核心逻辑是通过不等值关联完成行展开，涉及的表结构约定如下：

• 原始业务表source_data：必须携带可标识原始顺序的字段（如自增主键id），存储待展开数值的字段为n，初始化数据如下：

  id	n
  1	3
  2	3
  3	4
  4	1

• 序列辅助表num_seq：存储从1开始的连续无重复正整数，字段为seq_val，提前预置的行数需大于等于业务场景下n字段的最大值（本场景最大为4，常规业务预置到1000即可覆盖绝大多数需求）

标准实现SQL #

SELECT
    s.seq_val AS result
FROM
    source_data t
INNER JOIN num_seq s
    -- 关联条件控制每个n只关联1到n的序列值
    ON s.seq_val <= t.n
-- 双字段排序保证输出顺序和要求完全一致
ORDER BY
    t.id,
    s.seq_val;

无实体序列表兼容写法 #

如果不方便提前创建实体序列表，可使用递归CTE临时生成所需序列，兼容MySQL 8.0+、PostgreSQL、SQL Server等支持递归CTE的数据库：

WITH RECURSIVE num_seq AS (
    -- 递归起点：从1开始
    SELECT 1 AS seq_val
    UNION ALL
    -- 递归生成连续整数，上限为原始表n字段的最大值
    SELECT seq_val + 1 FROM num_seq WHERE seq_val < (SELECT MAX(n) FROM source_data)
)
SELECT
    s.seq_val AS result
FROM
    source_data t
INNER JOIN num_seq s
    ON s.seq_val <= t.n
ORDER BY
    t.id,
    s.seq_val;

注意事项 #

• 排序逻辑不可省略：SQL执行不带ORDER BY时，数据库不保证返回结果的顺序，会出现序列错乱
• 序列完整性校验：不管是实体序列表还是CTE生成的临时序列，都要保证序列连续无缺项、最大值覆盖业务中n的最大值，否则会出现展开行数不全的问题

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