SQL Server中获取Flag从No转Yes的最新日期及范围判定

这是个典型的状态变更追踪需求，我帮你设计一个清晰的实现方案，用窗口函数就能高效搞定👇

核心需求拆解 #

咱们先把需求拆得明明白白：

• 对每个ID，首先得确认它最新的Flag状态是不是Yes，如果是No直接返回null
• 如果最新状态是Yes，要找到它最后一次从No切换到Yes的日期

完整SQL代码 #

假设你的表名叫status_records，直接用下面的代码就能得到结果（支持MySQL 8+、PostgreSQL、SQL Server等支持窗口函数的数据库）：

WITH status_sequence AS (
    SELECT 
        ID,
        Date,
        Flag,
        -- 获取当前记录的上一条Flag状态
        LAG(Flag) OVER (PARTITION BY ID ORDER BY Date) AS previous_flag,
        -- 标记每个ID的最新记录（排第一的就是最新的）
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date DESC) AS row_rank
    FROM status_records
),
latest_flag_info AS (
    -- 提取每个ID的最新状态
    SELECT ID, Flag AS latest_flag
    FROM status_sequence
    WHERE row_rank = 1
)
-- 第一步：找出所有从No→Yes的变更记录，取每个ID的最新变更日期
SELECT 
    ss.ID,
    CASE WHEN lfi.latest_flag = 'Yes' THEN MAX(ss.Date) ELSE NULL END AS last_no_to_yes_date
FROM status_sequence ss
JOIN latest_flag_info lfi ON ss.ID = lfi.ID
WHERE ss.Flag = 'Yes' AND ss.previous_flag = 'No'
GROUP BY ss.ID, lfi.latest_flag

-- 第二步：处理那些从未有过No→Yes变更，但最新状态是Yes的ID（比如一开始就是Yes的情况）
UNION ALL
SELECT 
    ID,
    CASE WHEN latest_flag = 'Yes' THEN MIN(Date) ELSE NULL END AS last_no_to_yes_date
FROM latest_flag_info
WHERE ID NOT IN (SELECT ID FROM status_sequence WHERE Flag = 'Yes' AND previous_flag = 'No')
GROUP BY ID, latest_flag
ORDER BY ID;

代码逻辑解释 #

1. status_sequence CTE：

   • 用LAG函数拿到每条记录的上一个Flag状态，这样就能精准识别出No→Yes的变更点
   • 用ROW_NUMBER给每个ID的记录按日期倒序排号，方便后续快速提取最新状态

2. latest_flag_info CTE：

   • 直接筛选出每个ID的最新记录（row_rank=1），拿到它的最终状态，这是判断是否返回日期的核心依据

3. 主查询：

   • 第一部分：筛选出所有No→Yes的变更记录，按ID分组取最大的日期，再结合最新状态判断是否返回该日期
   • 第二部分：补充处理那些一开始就是Yes、从未有过No状态的ID，这种情况如果最新状态是Yes，就返回它最早的Yes日期（如果需求里不需要覆盖这种场景，可以直接删掉这部分）

对应你的示例验证 #

• 对于ID1：No→Yes的变更日期是2016-01-04和2018-01-01，最新状态是Yes，所以取最大的2018-01-01，完全符合预期
• 对于ID2：最新状态是No，所以直接返回null，也完美匹配要求

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