SQL按唯一条件筛选行：查询首条记录Prev为SC的ID的全量明细

核心思路 #

要得到目标结果，只需要按三步处理：

1. 给每个ID下的所有记录按插入时间Time_inserted升序排序，标记出每个ID的第一条插入记录
2. 筛选出第一条记录的Prev字段值为SC的所有ID
3. 关联原表取出符合条件的ID的全部记录

可直接运行的SQL代码 #

WITH id_first_record AS (
    SELECT 
        id,
        -- 按ID分组，按插入时间升序排序，标记每个ID的第一条记录
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY Time_inserted ASC) AS rn,
        Prev
    FROM Table1
),
qualified_ids AS (
    -- 筛选出第一条记录Prev为SC的ID
    SELECT DISTINCT id
    FROM id_first_record
    WHERE rn = 1 AND Prev = 'SC'
)
-- 关联原表取出这些ID的全部记录
SELECT t.*
FROM Table1 t
INNER JOIN qualified_ids q ON t.id = q.id
ORDER BY t.id, t.Time_inserted ASC;

原有代码的问题说明 #

你之前的代码存在多处逻辑错误：

• CTE1直接筛选所有Prev='SC'的ID，没有判断是否为该ID的第一条记录，会把ID1这类后续记录出现SC的ID也误算进去
• 给行号分组时partition by a.id, time_inserted相当于每个ID的每个时间点单独分组，行号永远为1，完全起不到排序取第一条的作用
• 中间的data子查询加了where prev = 'SC'的过滤条件，直接把同ID下非SC的其他记录全部筛掉了

如果使用的数据库不支持CTE语法，也可以用子查询实现，逻辑和上面完全一致：

SELECT t.*
FROM Table1 t
WHERE t.id IN (
    SELECT id
    FROM (
        SELECT 
            id,
            ROW_NUMBER() OVER (PARTITION BY id ORDER BY Time_inserted ASC) AS rn,
            Prev
        FROM Table1
    ) tmp
    WHERE rn = 1 AND Prev = 'SC'
)
ORDER BY t.id, t.Time_inserted ASC;

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