如何获取指定日期下各ID最新startdate且end date非空的记录

需求说明 #

筛选指定日期范围内，end date不为空的记录，对每个ID仅保留startdate最新的那一条。

原始数据表 #

期望输出 #

解决方案 #

使用窗口函数ROW_NUMBER()可以精准实现需求，步骤如下：

1. 过滤end date为空的记录，同时限定目标日期
2. 按ID分组，每组内按startdate倒序标记序号
3. 仅保留每组序号为1的记录（即最新记录）

通用SQL代码 #

WITH ranked_records AS (
    SELECT 
        ID,
        status,
        startdate,
        `end date`,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY startdate DESC) AS rn
    FROM your_table_name
    WHERE 
        `end date` IS NOT NULL
        AND DATE(startdate) = '2023-03-01' -- 替换为你的目标日期
)
SELECT ID, status, startdate, `end date`
FROM ranked_records
WHERE rn = 1
ORDER BY ID;

代码细节说明 #

• PARTITION BY ID：确保每个ID单独分组排序
• ORDER BY startdate DESC：让最新的记录排在组内第一位，序号标记为1
• DATE(startdate)：提取日期部分筛选指定日期的记录，不同数据库语法略有差异：
  • MySQL：DATE(startdate)
  • SQL Server：CAST(startdate AS DATE)
  • Oracle：TRUNC(startdate)

简化版本（支持QUALIFY子句的数据库） #

如果使用BigQuery、Snowflake等支持QUALIFY的数据库，可省略CTE，直接写：

SELECT 
    ID,
    status,
    startdate,
    `end date`
FROM your_table_name
WHERE 
    `end date` IS NOT NULL
    AND DATE(startdate) = '2023-03-01'
QUALIFY ROW_NUMBER() OVER (PARTITION BY ID ORDER BY startdate DESC) = 1
ORDER BY ID;

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