SQL技术问询：基于分组内特定值的连续状态标记实现

安全阿岳

2026-7-26

解决方案

要实现标记4月22日状态为'Remove from BD'的账户，以及该账户后续所有行，你可以通过窗口函数或关联子查询实现，以下是两种常用方案：

方案一：窗口函数（高效推荐）

利用MAX() OVER()窗口函数先判断每个账户是否在目标日期出现过指定状态，再结合日期条件标记后续行：

SELECT
    account_id,
    status_date,
    status,
    -- 满足条件则标记，否则留空
    CASE
        WHEN has_remove_event = 1 AND status_date >= '2024-04-22' THEN '标记'
        ELSE ''
    END AS is_marked
FROM (
    SELECT
        *,
        -- 按账户分组，判断该账户是否存在4月22日的'Remove from BD'状态
        MAX(CASE 
                WHEN DATE(status_date) = '2024-04-22' 
                     AND status = 'Remove from BD' 
                THEN 1 
                ELSE 0 
            END) OVER(PARTITION BY account_id) AS has_remove_event
    FROM account_status -- 替换为你的表名
) AS sub_query
ORDER BY account_id, status_date;

方案二：关联子查询

通过子查询直接校验当前账户是否符合条件，逻辑更直观：

SELECT
    main.account_id,
    main.status_date,
    main.status,
    CASE
        WHEN EXISTS (
            SELECT 1
            FROM account_status AS sub
            WHERE sub.account_id = main.account_id
              AND DATE(sub.status_date) = '2024-04-22'
              AND sub.status = 'Remove from BD'
              AND main.status_date >= '2024-04-22'
        ) THEN '标记'
        ELSE ''
    END AS is_marked
FROM account_status AS main -- 替换为你的表名
ORDER BY account_id, status_date;