如何用SQL基于上一条记录的FromDate生成ToDate列？

需求明确：按ID分组，每个ID下的记录按FromDate从新到旧排序，每条记录的ToDate取下一条记录的FromDate减1天；最新的那条记录（排序后的第一条）ToDate固定设为'9999-12-31'。

样本数据集 #

ID| FromDate 
1 | 2022-02-08
1 | 2022-01-05
1 | 2022-01-02
2 | 2022-04-03
2 | 2022-01-07
2 | 2021-12-04

核心思路 #

利用窗口函数LEAD()，按ID分组、FromDate降序排序，获取当前记录的下一条记录的FromDate；对该日期做减1天处理，最后用COALESCE()将最新记录的NULL值替换为指定默认日期。

不同数据库的实现代码 #

MySQL/MariaDB

SELECT 
    ID,
    FromDate,
    COALESCE(DATE_SUB(LEAD(FromDate) OVER (PARTITION BY ID ORDER BY FromDate DESC), INTERVAL 1 DAY), '9999-12-31') AS ToDate
FROM your_table
ORDER BY ID, FromDate DESC;

PostgreSQL

SELECT 
    ID,
    FromDate,
    COALESCE(LEAD(FromDate) OVER (PARTITION BY ID ORDER BY FromDate DESC) - INTERVAL '1 day', '9999-12-31'::DATE) AS ToDate
FROM your_table
ORDER BY ID, FromDate DESC;

SQL Server

SELECT 
    ID,
    FromDate,
    COALESCE(DATEADD(DAY, -1, LEAD(FromDate) OVER (PARTITION BY ID ORDER BY FromDate DESC)), '9999-12-31') AS ToDate
FROM your_table
ORDER BY ID, FromDate DESC;

预期输出 #

ID| FromDate   | ToDate
1 | 2022-02-08 | 9999-12-31
1 | 2022-01-05 | 2022-02-07
1 | 2022-01-02 | 2022-01-04
2 | 2022-04-03 | 9999-12-31
2 | 2022-01-07 | 2022-04-02
2 | 2021-12-04 | 2022-01-06

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