如何用单条SQL查询获取当前日期对应的目标行？

核心需求回顾 #

• 仅返回当前日期（2022-10-24）落在日期区间内的行，其中endDate为NULL时视为区间未结束（即只要startDate≤当前日期即符合条件）
• 若存在多条符合条件的行，优先选择endDate为NULL的记录；当startDate相同时，该优先级依然生效

最终SQL语句 #

WITH filtered_rows AS (
    SELECT 
        id,
        startDate,
        endDate,
        ROW_NUMBER() OVER (
            ORDER BY 
                CASE WHEN endDate IS NULL THEN 0 ELSE 1 END,
                startDate DESC
        ) AS rn
    FROM your_table_name
    WHERE 
        startDate <= '2022-10-24'
        AND (endDate IS NULL OR endDate >= '2022-10-24')
)
SELECT id, startDate, endDate
FROM filtered_rows
WHERE rn = 1;

逻辑拆解 #

1. 筛选符合日期条件的行

   • 用startDate <= '2022-10-24'确保记录的起始日期不晚于当前日期
   • 用(endDate IS NULL OR endDate >= '2022-10-24')覆盖两种结束情况：要么区间未结束（NULL），要么结束日期在当前日期之后

2. 排序与优先级控制

   • CASE WHEN endDate IS NULL THEN 0 ELSE 1 END：将endDate为NULL的记录标记为0，其他为1，升序排序后NULL记录会排在最前面，满足优先级要求
   • startDate DESC：当存在多条非NULL结束日期的符合记录时，优先选择起始日期最晚的（可根据实际需求调整，若不需要可移除）

3. 取唯一结果

   • 用ROW_NUMBER()给排序后的记录编号，取编号为1的行即为符合要求的目标记录

场景验证 #

• 场景1：id1符合日期条件，id2的startDate晚于当前日期被筛选掉，最终返回id1
• 场景2：id1和id2均符合日期条件，排序后id2（endDate为NULL）排在第一位，最终返回id2

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