如何仅获取status变更后的行？SQL查询需求求助

嘿，这个问题我太熟悉了！你完全不需要用分组，窗口函数才是解决这类“行与行对比”需求的完美工具。先理清楚你的场景：

你有一张test表，结构和插入数据如下：

表结构 #

CREATE TABLE test ( id INT, time datetime, status integer );

插入数据 #

INSERT INTO test (id, time, status) VALUES (1, '2020-11-09 10:00', 256);
INSERT INTO test (id, time, status) VALUES (2, '2020-11-09 11:00', 256);
INSERT INTO test (id, time, status) VALUES (3, '2020-11-09 11:20', 512);
INSERT INTO test (id, time, status) VALUES (4, '2020-11-09 11:35', 512);
INSERT INTO test (id, time, status) VALUES (5, '2020-11-09 11:40', 1024);
INSERT INTO test (id, time, status) VALUES (6, '2020-11-09 11:45', 1024);
INSERT INTO test (id, time, status) VALUES (7, '2020-11-09 11:48', 1024);
INSERT INTO test (id, time, status) VALUES (8, '2020-11-09 12:00', 0);
INSERT INTO test (id, time, status) VALUES (9, '2020-11-09 12:01', 0);
INSERT INTO test (id, time, status) VALUES (10, '2020-11-09 12:05', 0);
INSERT INTO test (id, time, status) VALUES (11, '2020-11-09 12:07', 0);
INSERT INTO test (id, time, status) VALUES (12, '2020-11-09 12:09', 512);

你的需求是仅获取status发生变更的行，期望结果是id为1、3、5、8、12的记录。用SELECT DISTINCT(status), time FROM test;肯定不行——DISTINCT只会去重，但没法识别连续相同状态的起始行，会把同一状态的所有不同时间都列出来，不符合你的需求。

解决方案：用窗口函数LAG()实现 #

这里的核心是要对比当前行和上一行的status值，找出两者不同的行（包括第一行，因为它是状态的起点）。LAG()窗口函数正好能帮我们拿到上一行的status值，完美适配这个场景。

具体SQL语句如下：

SELECT id, time, status
FROM (
    SELECT 
        id, 
        time, 
        status,
        -- 按时间排序，获取上一行的status值
        LAG(status) OVER (ORDER BY time) AS prev_status
    FROM test
) AS sub
-- 筛选条件：要么是第一行（没有上一行），要么当前行和上一行status不同
WHERE prev_status IS NULL OR status != prev_status;

逻辑拆解 #

1. 子查询里，LAG(status) OVER (ORDER BY time)会按照time的顺序，给每一行返回它前一行的status；第一行没有上一行，所以prev_status是NULL。
2. 外层查询只保留两种行：
   • prev_status IS NULL：也就是第一行，它是第一个状态的起始，必须保留。
   • status != prev_status：当前行的状态和上一行不一样，说明状态发生了变更，这正是你要找的行。

执行这个SQL后，就能精准得到你想要的id为1、3、5、8、12的所有记录。

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