SQL实现：基于step列条件填充自定义code列

我有一个带筛选条件的多表关联查询，语句如下：

select x, step, code, ....,
from 
table1
inner join table2
inner join table3
where
'filters'
order by x, code

该查询返回的结果集如下：

其中step=0代表新流程的起始，后续非0的step属于该流程的环节。需要提取每个流程起始（step=0）的code值，填充到该流程的所有后续行中，直到遇到下一个step=0的新流程。可以新增列实现，无需替换原code列，期望结果如下：

核心思路是通过分组标记流程范围，再提取每组起始点的code值填充全组。以下是几种兼容不同数据库的实现方式：

1. 窗口函数直接填充（MySQL 8.0+/PostgreSQL/SQL Server） #

利用LAST_VALUE窗口函数，取当前行及之前最近的step=0对应的code值：

SELECT 
    x, 
    step, 
    code,
    LAST_VALUE(CASE WHEN step = 0 THEN code END) OVER (
        PARTITION BY x 
        ORDER BY step 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS flow_code
FROM (
    -- 嵌入原多表关联查询
    select x, step, code, ....
    from table1
    inner join table2
    inner join table3
    where 'filters'
) t
ORDER BY x, step;

2. 分组ID关联法（兼容低版本数据库） #

先给每个流程分配唯一组ID，再关联取组内起始code：

WITH flow_groups AS (
    SELECT 
        x, 
        step, 
        code,
        -- 累计统计当前行之前的step=0次数，作为流程组ID
        SUM(CASE WHEN step = 0 THEN 1 ELSE 0 END) OVER (
            PARTITION BY x 
            ORDER BY step 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM (
        select x, step, code, ....
        from table1
        inner join table2
        inner join table3
        where 'filters'
    ) t
),
flow_start_codes AS (
    SELECT x, group_id, code AS flow_code
    FROM flow_groups
    WHERE step = 0
)
SELECT 
    fg.x, 
    fg.step, 
    fg.code, 
    fsc.flow_code
FROM flow_groups fg
JOIN flow_start_codes fsc 
    ON fg.x = fsc.x AND fg.group_id = fsc.group_id
ORDER BY fg.x, fg.step;

注意事项 #

• 若流程的分组依据不止x，需调整PARTITION BY后的字段列表
• 所有方案均保留原code列，新增的flow_code为填充后的起始流程code

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