SQL查询：如何筛选当前状态为1及曾为1现已变更的RecordId

方案实现逻辑 #

要实现需求，核心要先拿到两个关键判断条件：

• 每个RecordId的当前状态：即该RecordId下CreatedDate最新的记录对应的Status值
• 每个RecordId是否历史上存在过Status = 1的记录

完整SQL示例 #

使用窗口函数实现（兼容MySQL 8.0+/SQL Server/PostgreSQL等支持标准SQL的数据库）：

WITH record_status AS (
    -- 预处理每个RecordId的状态标记
    SELECT 
        RecordId,
        -- 取每个RecordId最新的Status作为当前状态
        FIRST_VALUE(Status) OVER (PARTITION BY RecordId ORDER BY CreatedDate DESC) AS current_status,
        -- 标记该RecordId是否历史有过Status=1
        MAX(CASE WHEN Status = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY RecordId) AS has_history_status_1
    FROM Table1
),
distinct_record AS (
    -- 去重得到每个RecordId的唯一标记
    SELECT DISTINCT RecordId, current_status, has_history_status_1 
    FROM record_status
)
-- 第一组：当前Status为1的RecordId
SELECT '第一组' AS group_type, RecordId 
FROM distinct_record 
WHERE current_status = 1
UNION ALL
-- 第二组：历史有过Status=1但当前状态已变更的RecordId
SELECT '第二组' AS group_type, RecordId 
FROM distinct_record 
WHERE has_history_status_1 = 1 AND current_status != 1;

单独查询两组数据的写法 #

如果需要分别查询两组数据，可以拆分SQL：

第一组查询（当前状态为1的RecordId）

SELECT DISTINCT t1.RecordId
FROM Table1 t1
INNER JOIN (
    SELECT RecordId, MAX(CreatedDate) AS latest_date
    FROM Table1
    GROUP BY RecordId
) t2 ON t1.RecordId = t2.RecordId AND t1.CreatedDate = t2.latest_date
WHERE t1.Status = 1;

第二组查询（历史有过Status=1但当前状态不为1的RecordId）

SELECT DISTINCT t1.RecordId
FROM Table1 t1
INNER JOIN (
    SELECT RecordId, MAX(CreatedDate) AS latest_date
    FROM Table1
    GROUP BY RecordId
) t2 ON t1.RecordId = t2.RecordId AND t1.CreatedDate = t2.latest_date
WHERE t1.Status != 1
-- 筛选历史存在过Status=1的记录
AND EXISTS (SELECT 1 FROM Table1 t3 WHERE t3.RecordId = t1.RecordId AND t3.Status = 1);

查询结果验证 #

和你提供的示例数据匹配，查询结果为：

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