MS SQL 2016查询用户最后一次状态1后的所有状态2记录

刚好之前处理过类似的状态日志查询需求，给你分享两种适配MS SQL 2016的实现方式，完全贴合你的规则：

这种写法把逻辑拆成几个独立步骤，便于理解和后续维护：

WITH UserLastStatus1 AS (
    -- 第一步：找出每个用户最后一次状态为1的记录ID
    SELECT 
        [USER],
        MAX(ID) AS LastStatus1ID
    FROM YourTableName
    WHERE STATUS = 1
    GROUP BY [USER]
),
UserLatestInfo AS (
    -- 第二步：获取每个用户的最新记录ID和对应的状态
    SELECT 
        [USER],
        MAX(ID) AS MaxID,
        -- 通过窗口函数拿到最新ID对应的状态
        MAX(CASE WHEN ID = MAX(ID) THEN STATUS END) OVER (PARTITION BY [USER]) AS LatestStatus
    FROM YourTableName
    GROUP BY [USER]
)
-- 第三步：筛选符合条件的记录
SELECT t.ID, t.STATUS, t.[USER]
FROM YourTableName t
JOIN UserLastStatus1 u1 ON t.[USER] = u1.[USER]
JOIN UserLatestInfo ul ON t.[USER] = ul.[USER]
WHERE 
    -- 只处理最新状态为2的用户
    ul.LatestStatus = 2
    -- 只取最后一次状态1之后的记录
    AND t.ID > u1.LastStatus1ID
    -- 确保是状态2的记录
    AND t.STATUS = 2
ORDER BY t.[USER], t.ID;

如果追求代码紧凑，这种写法也能实现同样的效果，还额外兼容“用户从未有过状态1”的场景：

SELECT t.ID, t.STATUS, t.[USER]
FROM YourTableName t
WHERE 
    -- 先确认用户的最新状态是2
    EXISTS (
        SELECT 1
        FROM YourTableName t_max
        WHERE t_max.[USER] = t.[USER]
        GROUP BY t_max.[USER]
        HAVING MAX(CASE WHEN ID = MAX(ID) THEN STATUS END) = 2
    )
    AND t.STATUS = 2
    -- 取所有ID大于最后一次状态1的记录（无状态1时取0，即全选状态2）
    AND t.ID > (
        SELECT ISNULL(MAX(ID), 0)
        FROM YourTableName t1
        WHERE t1.[USER] = t.[USER] AND t1.STATUS = 1
    )
ORDER BY t.[USER], t.ID;

针对你提到的场景验证： #

1. 场景1：最后一次状态1后有多条状态2
   比如用户记录为ID1(状态1)、ID2(状态2)、ID3(状态2)，两种方案都会返回ID2和ID3，完全符合需求。
2. 场景2：最新状态切换为1
   比如用户记录为ID1(状态2)、ID2(状态1)，EXISTS或UserLatestInfo会判定最新状态为1，因此不会返回任何记录。
3. 场景3：最后一次状态1后仅一条状态2
   比如用户记录为ID1(状态1)、ID2(状态2)、ID3(状态1)、ID5(状态2)，方案会识别最后一次状态1的ID是3，因此返回ID5。

关键逻辑说明： #

• 因为ID是标识列（自增），所以MAX(ID)就代表最新的记录，这是整个查询的核心依据。
• 用MAX(CASE WHEN ID = MAX(ID) THEN STATUS END)来精准获取最新记录的状态，避免了额外的关联操作。
• 方案二中的ISNULL(MAX(ID), 0)是为了兼容用户从未有过状态1的情况，如果不需要这个兼容，可以去掉ISNULL，此时无状态1的用户不会被返回。

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