如何编写SQL从含生效失效日期的编码表中筛选最新有效编码

前提说明 #

• 假设你使用的表名为code_validity，可自行替换为实际表名
• 以下方案默认Start Date、End Date为数据库可直接比较的日期类型，若为示例中dd/mm/yy格式的字符串，需先转换为日期类型再做比较
• 兼容所有支持标准SQL的主流数据库（MySQL 8.0+/PostgreSQL/SQL Server/Oracle等）

核心查询语句 #

SELECT ID, Code, `Start Date`, `End Date`
FROM code_validity
WHERE
    -- 筛选已生效的记录
    `Start Date` <= CURRENT_DATE
    -- 筛选未过期的记录：失效日期为空 或 失效日期晚于当前日期
    AND (`End Date` IS NULL OR `End Date` > CURRENT_DATE);

适配字符串日期的版本（以MySQL为例） #

如果你的日期字段是和示例一致的dd/mm/yy格式字符串，使用如下语句：

SELECT ID, Code, `Start Date`, `End Date`
FROM code_validity
WHERE
    STR_TO_DATE(`Start Date`, '%d/%m/%y') <= CURRENT_DATE
    AND (
        `End Date` IS NULL 
        OR STR_TO_DATE(`End Date`, '%d/%m/%y') > CURRENT_DATE
    );

逻辑说明 #

以上语句天然匹配你提出的两条规则：

• 同一编码的多条符合筛选条件的记录，必然都包含当前日期，属于日期范围重叠的有效记录，会全部返回，符合规则1
• 若同一编码的记录为前后衔接无重叠的状态，旧记录的失效日期必然早于当前日期，会被过滤，仅保留最新的有效记录，符合规则2

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