请求协助：基于员工、生效日期及文件顺序生成连续序列号

问题背景 #

需基于employee_code、effective_date及文件接收顺序file_sequence生成连续序列号，核心规则：

• 按file_sequence排序后，连续出现的相同employee_code + effective_date组合需分配同一个序列号
• 序列号必须是连续无间隔的整数（如1、2、3...，不能出现跳号）

表结构 #

假设源表结构如下：

CREATE TABLE employee_data (
    employee_code VARCHAR(50),
    effective_date DATE,
    file_sequence INT
);

测试数据 #

INSERT INTO employee_data VALUES
('EMP001', '2024-01-01', 1),
('EMP001', '2024-01-01', 2),
('EMP002', '2024-01-01', 3),
('EMP001', '2024-01-01', 4),
('EMP003', '2024-02-01', 5),
('EMP003', '2024-02-01', 6);

现有错误SQL示例 #

常见错误写法（仅按组合直接排名，无法区分非连续的同组合）：

SELECT 
    employee_code,
    effective_date,
    file_sequence,
    DENSE_RANK() OVER (ORDER BY employee_code, effective_date) AS final_process_sequence
FROM employee_data
ORDER BY file_sequence;

输出对比 #

当前错误输出

预期输出

修正后的SQL #

核心思路：先标记连续分组的边界，再通过累加标记生成连续序列号

WITH grouped_data AS (
    SELECT 
        employee_code,
        effective_date,
        file_sequence,
        -- 标记新分组：当前行与上一行组合不同时，标记为1
        CASE 
            WHEN CONCAT_WS('|', employee_code, effective_date) = 
                 LAG(CONCAT_WS('|', employee_code, effective_date)) OVER (ORDER BY file_sequence)
            THEN 0
            ELSE 1
        END AS is_new_group
    FROM employee_data
),
sequence_data AS (
    SELECT 
        *,
        -- 累加新分组标记，生成连续序列号
        SUM(is_new_group) OVER (ORDER BY file_sequence ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS final_process_sequence
    FROM grouped_data
)
SELECT 
    employee_code,
    effective_date,
    file_sequence,
    final_process_sequence
FROM sequence_data
ORDER BY file_sequence;

代码解释 #

1. grouped_data CTE：使用LAG()函数对比当前行与前一行的employee_code + effective_date组合，用is_new_group标记是否为新分组（1=新分组，0=同分组）。使用CONCAT_WS避免字符拼接歧义。
2. sequence_data CTE：按file_sequence顺序累加is_new_group值，累加结果即为连续序列号——每遇到新分组，序列号自动加1，同一连续分组的序列号保持一致。
3. 最终查询按file_sequence排序，输出符合要求的结果。

注意事项 #

• 确保file_sequence是唯一且严格递增的，否则排序逻辑会失效。
• 若employee_code或effective_date包含特殊字符，CONCAT_WS的分隔符需选择不会出现在字段中的字符。

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