按Id分组筛选：优先取EndDate为NULL的记录，否则取MAX(EndDate)

问题描述 #

现有一张包含多列的表，需按以下规则筛选数据：

• 对每个Id，如果存在EndDate为NULL的记录，就选取该记录；
• 如果没有NULL的EndDate，则选取该Id对应EndDate最大的那条记录。

原始表数据 #

期望结果表 #

解决方案 #

方法1：窗口函数（推荐，灵活易扩展） #

用ROW_NUMBER()窗口函数给每个Id的记录排序，优先保留EndDate为NULL的记录，剩余记录按EndDate降序排列，最后取每个Id的第一条记录。该方案能保留原表所有列，且可轻松与其他表关联。

WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY Id 
            ORDER BY 
                CASE WHEN EndDate IS NULL THEN 0 ELSE 1 END,
                EndDate DESC
        ) AS rn
    FROM your_table_name
)
SELECT Id, EndDate, ...  -- 替换为实际需要的列名
FROM ranked_data
WHERE rn = 1;

• PARTITION BY Id：按Id分组处理每条记录
• ORDER BY中的CASE语句将EndDate为NULL的记录排在最前（序号0），其余记录按EndDate倒序排列
• 筛选rn=1即可得到每个Id符合要求的唯一记录

方法2：条件聚合+关联（适合简单场景） #

先统计每个Id的关键标记（是否存在NULL的EndDate）和最大EndDate，再关联原表筛选目标记录：

WITH id_flags AS (
    SELECT 
        Id,
        MAX(CASE WHEN EndDate IS NULL THEN 1 ELSE 0 END) has_null_enddate,
        MAX(EndDate) max_enddate
    FROM your_table_name
    GROUP BY Id
)
SELECT t.Id, t.EndDate, t....
FROM your_table_name t
JOIN id_flags f ON t.Id = f.Id
WHERE 
    (f.has_null_enddate = 1 AND t.EndDate IS NULL)
    OR (f.has_null_enddate = 0 AND t.EndDate = f.max_enddate);

• 聚合查询先获取每个Id的核心判断条件
• 通过关联原表，根据标记精准筛选符合规则的记录

关联其他表的扩展用法 #

两种方案都支持直接与其他表关联，以方法1为例，可在CTE阶段完成关联：

WITH ranked_data AS (
    SELECT 
        t.*,
        ot.*,  -- 替换为关联表需要的列
        ROW_NUMBER() OVER (
            PARTITION BY t.Id 
            ORDER BY 
                CASE WHEN t.EndDate IS NULL THEN 0 ELSE 1 END,
                t.EndDate DESC
        ) AS rn
    FROM your_table_name t
    LEFT JOIN other_table ot ON t.Id = ot.Id  -- 此处添加关联逻辑
)
SELECT *
FROM ranked_data
WHERE rn = 1;

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