如何查询展示各ID的NAME变更及起止日期（含今日收尾）

针对你的需求，原SQL存在两个核心问题：一是首条记录的NAME和FROM_DATE为空，二是最后一条记录的TO_DATE未替换为当日日期。以下是修正后的方案：

修正后的SQL语句 #

WITH ranked_records AS (
    SELECT 
        ID,
        NAME,
        CURRENT_DATE AS RECORD_DATE,
        "ORDER" AS RECORD_ORDER,
        -- 获取下一次NAME变更的日期
        LEAD(CURRENT_DATE) OVER (PARTITION BY ID ORDER BY "ORDER") AS NEXT_CHANGE_DATE,
        -- 标记当前ID的最后一条记录
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY "ORDER" DESC) = 1 AS IS_LAST_RECORD
    FROM MY_TABLE
)
SELECT 
    ID,
    NAME,
    -- 首条记录的FROM_DATE为自身记录日期，非首条取上一次记录日期
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY ID ORDER BY "ORDER") = 1 THEN RECORD_DATE
        ELSE LAG(RECORD_DATE) OVER (PARTITION BY ID ORDER BY "ORDER")
    END AS FROM_DATE,
    -- 最后一条记录TO_DATE设为当日，其余用下一次变更日期
    CASE 
        WHEN IS_LAST_RECORD THEN CURRENT_DATE()
        ELSE NEXT_CHANGE_DATE
    END AS TO_DATE
FROM ranked_records
ORDER BY ID, "ORDER";

关键修正说明 #

1. 解决首条记录空值问题

   • 放弃原SQL中LAG(NAME)的写法，直接保留原表的NAME字段，避免首条记录无前置数据导致的NULL
   • 首条记录的FROM_DATE设为自身的CURRENT_DATE（即该NAME最早生效的日期），非首条记录用LAG()取上一次变更的日期

2. 处理最后一条记录的TO_DATE

   • 通过ROW_NUMBER()标记每个ID的最后一条记录
   • 用CASE分支判断：如果是最后一条，替换为当日日期；否则用LEAD()获取的下一次变更日期作为TO_DATE

3. 统一排序逻辑

   • 原SQL中同时用CURRENT_DATE和ORDER排序，容易出现日期与记录顺序不一致的问题，统一使用需求中指定的ORDER字段排序，确保变更顺序准确

数据库兼容提示 #

不同数据库的当日日期函数略有差异，需根据实际环境调整：

• MySQL：CURDATE() 或 CURRENT_DATE()
• Oracle：SYSDATE
• SQL Server：GETDATE() 或 CURRENT_TIMESTAMP
• PostgreSQL：CURRENT_DATE

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