如何按日期与ID获取表中最新记录？含特殊场景处理

先把你的测试数据整理成更清晰的表格：

你的核心需求其实是优先级排序：未终止记录（term_date=0）的优先级最高，当某个ID下存在这类记录时直接选中；如果所有记录都是已终止状态，再取该ID下term_date最新的那条。下面分两种场景给出实现方案：

方案一：用窗口函数实现（兼容MySQL 8+、PostgreSQL、SQL Server等） #

这是最简洁直观的方式，用ROW_NUMBER()窗口函数给每条记录按规则排序，最后提取每个ID的第一条记录：

WITH ranked_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY ID 
            ORDER BY 
                -- 未终止记录排最前面
                CASE WHEN term_date = '0' THEN 1 ELSE 2 END,
                -- 已终止记录按term_date倒序，最新的优先
                term_date DESC
        ) AS rn
    FROM your_table_name
)
SELECT ID, eff_date, term_date
FROM ranked_records
WHERE rn = 1;

逻辑解释

• PARTITION BY ID：把数据按ID分组，每个ID单独处理；
• ORDER BY里的CASE语句：给未终止记录分配最高优先级（排序值1比2小，所以排在前面）；
• 已终止记录通过term_date DESC让最新的日期排在前面；
• ROW_NUMBER()会给每个ID组内的记录按规则编号，取rn=1就是我们要的目标记录。

测试结果

用你的数据跑这段SQL，得到的结果完全符合需求：

方案二：兼容低版本数据库（比如MySQL 5.x） #

如果你的数据库不支持窗口函数，可以用子查询+左连接的方式实现：

SELECT t1.*
FROM your_table_name t1
LEFT JOIN your_table_name t2
    ON t1.ID = t2.ID
    AND (
        -- 若t2是未终止记录，而t1不是，则t1会被排除
        (t2.term_date = '0' AND t1.term_date != '0')
        OR
        -- 若t2和t1都是已终止记录，但t2的term_date更新，则t1会被排除
        (t1.term_date != '0' AND t2.term_date != '0' AND t2.term_date > t1.term_date)
    )
WHERE t2.ID IS NULL;

逻辑解释

这个思路是：找到每个ID下不存在优先级更高的记录，剩下的就是我们需要的目标记录——要么是未终止的，要么是已终止里最新的。

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