MS SQL Server：为每行记录生成rate_effective_end_date的查询方法

刚好之前处理过类似的需求，用MS SQL Server的LEAD()窗口函数就能完美解决这个问题——它可以轻松获取同一员工的下一条薪资记录的生效日期，再做日期调整就能得到当前记录的结束日期。

基础版本查询 #

这个版本会给最后一条记录的rate_effective_end_date返回NULL（因为没有后续的生效日期）：

SELECT 
    emp_id,
    rate_effective_date,
    hourly_rate,
    -- 提取同一员工下一条记录的生效日期，减1天作为当前记录的结束日期
    DATEADD(DAY, -1, LEAD(rate_effective_date) OVER (PARTITION BY emp_id ORDER BY rate_effective_date)) AS rate_effective_end_date
FROM 
    YourTableName; -- 记得替换成你的实际数据表名称

带默认值的版本 #

如果希望最后一条记录的结束日期用一个固定值（比如表示永久生效的9999-12-31），可以用ISNULL()来处理NULL情况：

SELECT 
    emp_id,
    rate_effective_date,
    hourly_rate,
    ISNULL(
        DATEADD(DAY, -1, LEAD(rate_effective_date) OVER (PARTITION BY emp_id ORDER BY rate_effective_date)),
        '9999-12-31' -- 可根据业务需求替换成其他默认日期
    ) AS rate_effective_end_date
FROM 
    YourTableName;

关键逻辑说明 #

• PARTITION BY emp_id：确保我们只在同一个员工的记录组内查找下一条记录，不会跨员工混淆数据
• ORDER BY rate_effective_date：按薪资生效日期升序排序，保证LEAD()拿到的是该员工后续生效的薪资记录日期
• DATEADD(DAY, -1, ...)：把下一条记录的生效日期往前推1天，就是当前薪资标准的最后生效日

举个例子，对应你给出的样本数据，执行后会得到这样的结果：

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