SQL查询需求：筛选最新Begin Date且End Date为NULL的唯一ID记录

需求说明 #

• 仅当所有字段值均为NULL时才返回NULL记录；
• 从TableA表中筛选满足以下条件的唯一ID记录：
  • 对每个ID，仅当其最新Begin Date对应的End Date为NULL时，返回该条最新Begin Date的记录；
  • 若ID的最新Begin Date对应的End Date不为NULL，则直接排除该ID的所有记录。

示例数据 #

期望结果 #

注：ID12因最新Begin Date（9-2-2022）对应的End Date不为NULL被排除；每个ID仅返回一条唯一记录。

原SQL存在的问题 #

原SQL语句：

SELECT ID, MAX (Begin Date), End Date
FROM TableA
WHERE End Date IS NULL 
ORDER BY Begin Date DESC

存在以下问题：

1. 未按ID分组，MAX(Begin Date)计算的是全表最大值，而非每个ID的最新日期；
2. End Date与MAX(Begin Date)无关联，会随机选取一条End Date为NULL的记录，无法匹配最新Begin Date对应的那条；
3. 提前过滤End Date IS NULL会跳过对ID最新记录的判断，错误保留旧的End Date为NULL的记录（比如ID12的旧记录）。

修正后的SQL方案 #

方案一：使用窗口函数（推荐） #

通过ROW_NUMBER()为每个ID的记录按Begin Date倒序排名，取最新记录后判断其End Date状态：

WITH ranked_records AS (
    SELECT 
        ID,
        Begin_Date,
        End_Date,
        -- 按ID分组，Begin Date倒序排名，最新记录排第1
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Begin_Date DESC) AS record_rank
    FROM TableA
    -- 排除所有字段均为NULL的记录
    WHERE NOT (ID IS NULL AND Begin_Date IS NULL AND End_Date IS NULL)
)
SELECT ID, Begin_Date, End_Date
FROM ranked_records
-- 取每个ID的最新记录，且该记录的End Date为NULL
WHERE record_rank = 1 AND End_Date IS NULL

方案二：先获取最新日期再关联 #

先查询每个ID的最新Begin Date，再关联原表找到对应记录并判断End Date：

WITH latest_id_dates AS (
    SELECT 
        ID,
        MAX(Begin_Date) AS latest_begin_date
    FROM TableA
    WHERE ID IS NOT NULL -- 排除无意义的NULL ID分组
    GROUP BY ID
)
SELECT t.ID, t.Begin_Date, t.End_Date
FROM TableA t
JOIN latest_id_dates ld 
    ON t.ID = ld.ID AND t.Begin_Date = ld.latest_begin_date
WHERE t.End_Date IS NULL
-- 排除所有字段均为NULL的记录
AND NOT (t.ID IS NULL AND t.Begin_Date IS NULL AND t.End_Date IS NULL)

说明 #

两种方案均实现：

• 仅保留每个ID的最新记录；
• 仅当最新记录的End Date为NULL时才返回该ID；
• 排除所有字段均为NULL的无效记录。

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