求SQL更新语句：按START_DATE排序，用同ID下一条记录START_DATE设置当前END_DATE

核心思路 #

利用窗口函数LEAD()获取同ID分组内、按START_DATE排序后的下一条记录的START_DATE，仅更新非最后一条的记录（最后一条的LEAD结果为NULL，跳过更新，保留原END_DATE为12/31/9999）。

各数据库实现示例 #

MySQL 8.0+（支持CTE）

WITH ranked_records AS (
    SELECT 
        id,
        start_date,
        end_date,
        -- 按ID分组、START_DATE排序，取当前记录的下一条START_DATE
        LEAD(start_date) OVER (PARTITION BY id ORDER BY start_date) AS next_start_date,
        -- 若有主键（如record_id），建议带上用于精准关联
        record_id
    FROM your_table
)
UPDATE your_table t
JOIN ranked_records rr ON t.record_id = rr.record_id
SET t.end_date = rr.next_start_date
WHERE rr.next_start_date IS NOT NULL;

老版本MySQL（不支持CTE）

UPDATE your_table t
JOIN (
    SELECT 
        id,
        start_date,
        LEAD(start_date) OVER (PARTITION BY id ORDER BY start_date) AS next_start_date,
        record_id
    FROM your_table
) rr ON t.record_id = rr.record_id
SET t.end_date = rr.next_start_date
WHERE rr.next_start_date IS NOT NULL;

PostgreSQL

WITH ranked_records AS (
    SELECT 
        id,
        start_date,
        end_date,
        LEAD(start_date) OVER (PARTITION BY id ORDER BY start_date) AS next_start_date,
        record_id
    FROM your_table
)
UPDATE your_table t
SET end_date = rr.next_start_date
FROM ranked_records rr
WHERE t.record_id = rr.record_id
AND rr.next_start_date IS NOT NULL;

SQL Server

WITH ranked_records AS (
    SELECT 
        id,
        start_date,
        end_date,
        LEAD(start_date) OVER (PARTITION BY id ORDER BY start_date) AS next_start_date,
        record_id
    FROM your_table
)
UPDATE ranked_records
SET end_date = next_start_date
WHERE next_start_date IS NOT NULL;

重要注意事项 #

• 先验证再更新：执行UPDATE前，先运行以下SELECT语句确认结果是否符合预期：

SELECT 
    id,
    start_date,
    end_date,
    LEAD(start_date) OVER (PARTITION BY id ORDER BY start_date) AS expected_new_end_date
FROM your_table
ORDER BY id, start_date;

• 精准关联：如果表中无唯一主键，需确保id + start_date组合唯一，否则可能导致错误更新；若存在重复start_date，需添加额外排序字段（如创建时间）保证顺序唯一。
• 备份数据：执行更新操作前务必备份目标表数据，避免意外数据丢失。

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