SQL如何基于前一行条件创建episode_id列分组关联就诊记录

原有写法不生效的核心原因：LAG() 窗口函数仅能读取查询时原始表中已存储的字段值，无法递归引用同一条SELECT语句中逐行计算生成的新值。手动为第一行赋值1后，其余行的episode_id原始值均为NULL，LAG()读取到的上一行值全为NULL，自然无法生成正确的连续编号。

不需要写逐行遍历的循环逻辑，直接用窗口累计计数即可实现需求：
所有就诊周期的起点都是admission_type = 'index'的记录，只要按指定规则排序后，统计从第一行到当前行累计出现过多少条index类型的记录，这个累计值就是对应行的episode_id：

• 遇到index记录时，累计值+1，对应新的就诊周期
• 遇到transfer记录时，累计值和上一行保持一致，归属上一个就诊周期

1. 直接查询返回带正确episode_id的结果 #

如果需要和示例一致的全局连续episode_id（跨个人编号连续累加），使用如下代码：

SELECT
    COUNT(CASE WHEN admission_type = 'index' THEN 1 END)
        OVER (ORDER BY uli, admitdate_dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS episode_id,
    admitdate_dt,
    disdate_dt,
    uli,
    admission_type
FROM admissions
ORDER BY uli, admitdate_dt;

如果需要按个人独立编号（每个uli的就诊周期从1开始计数），只需要给窗口函数增加分区子句即可：

SELECT
    COUNT(CASE WHEN admission_type = 'index' THEN 1 END)
        OVER (PARTITION BY uli ORDER BY admitdate_dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS episode_id,
    admitdate_dt,
    disdate_dt,
    uli,
    admission_type
FROM admissions
ORDER BY uli, admitdate_dt;

2. 将计算结果更新回表的episode_id字段 #

以原有代码使用的Oracle语法为例，用MERGE语句关联更新即可：

-- 全局连续编号版本
MERGE INTO admissions t
USING (
    SELECT
        seqnum,
        COUNT(CASE WHEN admission_type = 'index' THEN 1 END)
            OVER (ORDER BY uli, admitdate_dt ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS calc_episode_id
    FROM admissions
) s
ON (t.seqnum = s.seqnum)
WHEN MATCHED THEN UPDATE SET t.episode_id = s.calc_episode_id;

如果需要按个人独立编号，把MERGE子查询里的窗口函数加上PARTITION BY uli即可。

用提供的示例数据运行全局编号版本的代码，会完全匹配预期输出：

• uli=001的第一条index记录，累计index数为1，后续两条transfer记录累计值保持1
• uli=002的第一条index记录，累计index数为2，第二条index记录累计值为3，后续transfer记录累计值保持3

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