请求调整SQL查询：当ServiceId变更时自动填充DateEnd

看起来你遇到的是典型的历史记录时间闭环问题——同一个实体（通过number标识）因为ServiceId变更产生了多条无结束日期的重复记录，需要自动给旧的ServiceId记录补上DateEnd，同时保留最新记录的DateEnd为NULL对吧？

我结合你的现有查询逻辑，给你两种解决方案：一种是查询时动态生成DateEnd（不修改原表数据），另一种是直接更新数据表（永久修正数据）。

方案1：查询时动态生成DateEnd（不修改原表） #

首先我们需要用窗口函数识别每个number下的ServiceId变更顺序，然后给旧记录推导结束日期。假设你的ods.Service表有DateStart字段（记录服务开始时间），如果没有的话可以换成记录创建时间（比如CreateTime）或者其他能排序的时间字段：

WITH ServiceHistory AS (
    SELECT 
        ods.number,
        ods.ServiceId,
        ods.DateStart,
        -- 获取同number下下一条记录的开始时间，用来推导当前记录的结束日期
        LEAD(ods.DateStart) OVER (PARTITION BY ods.number ORDER BY ods.DateStart) AS NextServiceStart,
        -- 标记当前记录是否为同number下的最新服务记录
        ROW_NUMBER() OVER (PARTITION BY ods.number ORDER BY ods.DateStart DESC) AS RowRank
    FROM ods.Service ods
)
SELECT 
    CONVERT(NVARCHAR(20), dbo.number) AS MainTableNumber,
    dbo.ServiceNumber,
    sh.ServiceId,
    sh.DateStart,
    -- 非最新记录：DateEnd设为下一条服务开始的前一天；最新记录保留NULL
    CASE 
        WHEN sh.RowRank > 1 THEN DATEADD(DAY, -1, sh.NextServiceStart)
        ELSE NULL 
    END AS DateEnd
FROM dbo.YourMainTable -- 替换成你的主表实际表名
RIGHT JOIN ServiceHistory sh 
    ON CONVERT(NVARCHAR(20), dbo.number) = sh.number
    AND sh.ServiceId = dbo.ServiceNumber
-- 如果需要只保留原DateEnd为NULL的记录，可添加以下条件
-- WHERE sh.DateEnd IS NULL

关键逻辑说明：

• PARTITION BY ods.number：按实体编号分组，确保我们只在同一个实体的记录间比较
• LEAD()函数：获取同组内下一条记录的DateStart，用来作为当前记录的结束时间节点
• ROW_NUMBER()：给同组内的记录按时间倒序排名，排名1的就是最新的服务记录，保持DateEnd为NULL

方案2：直接更新数据表（永久修正DateEnd） #

如果希望直接修正原表中的NULL值，而不是每次查询都计算，可以用CTE结合UPDATE语句：

WITH ServiceHistory AS (
    SELECT 
        DateEnd,
        LEAD(DateStart) OVER (PARTITION BY number ORDER BY DateStart) AS NextServiceStart,
        ROW_NUMBER() OVER (PARTITION BY number ORDER BY DateStart DESC) AS RowRank
    FROM ods.Service
    WHERE DateEnd IS NULL -- 只处理原DateEnd为NULL的记录
)
UPDATE ServiceHistory
SET DateEnd = DATEADD(DAY, -1, NextServiceStart)
WHERE RowRank > 1 -- 只更新非最新的服务记录

注意事项：

1. 如果你的表没有DateStart字段，无法通过时间排序的话，可以尝试用ServiceId的变更顺序（比如假设ServiceId是递增的），把ORDER BY DateStart换成ORDER BY ServiceId
2. 原查询中的CONVERT(NVARCHAR(20), dbo.number) = ods.number如果是因为数据类型不一致导致的，建议尽量统一两边的number数据类型（比如都改成INT或VARCHAR），避免转换带来的性能损耗
3. 执行UPDATE前建议先跑一遍SELECT看结果是否符合预期，避免误操作

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