SQL实现同列数据拆分至多列：按分组标识匹配后续行对应值

核心实现思路 #

原数据的分组标识和对应数值存在同一列、按固定顺序排列，实现只需要3步：

• 按数据原始存储顺序给每一行生成连续行号，必须保证行号顺序和数据排列顺序完全一致，如果原表有自增主键、入库时间字段，优先用这类字段排序生成行号，避免顺序错乱
• 识别列中值为1、2、3的分组标识行，将分组标识向下填充到紧随其后的3行数据上，作为每行数值对应的分组号
• 过滤掉本身是分组标识的行，剩下的数值和对应分组号就是目标结果

通用SQL示例（适用于MySQL8.0+、PostgreSQL、BigQuery等支持窗口函数的数据库） #

WITH sorted_data AS (
    -- 生成带连续行号的数据集，有明确排序字段请替换ORDER BY后的内容
    SELECT
        Column1,
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_num
    FROM your_table_name
),
fill_group_id AS (
    SELECT
        Column1,
        -- 取当前行及之前最后一个非空的分组标识，作为当前行所属分组
        LAST_VALUE(CASE WHEN Column1 IN (1,2,3) THEN Column1 END) IGNORE NULLS OVER (
            ORDER BY row_num
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS Column2
    FROM sorted_data
)
-- 过滤分组标识行，输出最终结果
SELECT Column1, Column2
FROM fill_group_id
WHERE Column1 NOT IN (1,2,3)
ORDER BY row_num;

低版本兼容写法（不支持IGNORE NULLS语法的数据库可用，比如早期版本SQL Server） #

WITH sorted_data AS (
    SELECT
        Column1,
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS row_num
    FROM your_table_name
),
mark_group AS (
    SELECT
        Column1,
        row_num,
        -- 每遇到一个分组标识，分组序号+1
        SUM(CASE WHEN Column1 IN (1,2,3) THEN 1 ELSE 0 END) OVER (
            ORDER BY row_num
            ROWS UNBOUNDED PRECEDING
        ) AS group_seq
    FROM sorted_data
),
group_map AS (
    -- 建立分组序号和分组标识的映射关系
    SELECT
        group_seq,
        Column1 AS Column2
    FROM mark_group
    WHERE Column1 IN (1,2,3)
)
SELECT
    m.Column1,
    g.Column2
FROM mark_group m
LEFT JOIN group_map g ON m.group_seq = g.group_seq
WHERE m.Column1 NOT IN (1,2,3)
ORDER BY m.row_num;

说明：如果后续分组标识不止1、2、3，只要是和业务数值有明确区分的标识值，只需要修改CASE语句里的判断条件即可，不需要调整其他核心逻辑。

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