在Microsoft SQL Server中生成Clinical Identifier列（非CTE，OTHER分组）

实现逻辑 #

1. 非OTHER类型的记录按行顺序生成独立递增ID
2. OTHER类型记录仅在Clinical Size Type Description Language为EN时开启新分组，后续所有OTHER记录归入当前分组，直到下一条EN标识的OTHER记录出现

SQL 代码 #

SELECT 
    [Clinical Size Type(s)],
    [Clinical Size Type Description],
    [Clinical Size Type Description Language],
    -- 生成最终Clinical Identifier
    CASE 
        WHEN [Clinical Size Type(s)] <> 'OTHER' 
            THEN row_num_non_other
        ELSE 
            base_id + other_group_num
    END AS [Clinical Identifier]
FROM (
    SELECT 
        *,
        -- 给非OTHER行按顺序编号
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row_num_non_other,
        -- 累计OTHER分组的起始标识数（EN的OTHER记为1，其余为0，求和得到分组编号）
        SUM(CASE 
                WHEN [Clinical Size Type(s)] = 'OTHER' 
                     AND [Clinical Size Type Description Language] = 'EN' 
                THEN 1 
                ELSE 0 
            END) OVER (ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS other_group_num,
        -- 统计非OTHER总行数，作为OTHER分组ID的起始基数
        (SELECT COUNT(*) FROM YourTableName WHERE [Clinical Size Type(s)] <> 'OTHER') AS base_id
    FROM YourTableName
) AS subquery
ORDER BY [Clinical Identifier];

代码说明 #

• ORDER BY (SELECT NULL)：请替换为表中实际的排序字段（如主键、创建时间等），确保行顺序与示例一致
• YourTableName：替换为你的目标表名
• 非OTHER记录ID从1开始递增，OTHER分组ID从非OTHER记录总数+1起始，每个新的EN标识行对应一个新ID值

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