SQL Server中捕获分类表两列数据变更的实现方法

没问题，我来帮你搞定这个两列变更捕获的需求！核心思路是利用SQL Server的窗口函数LAG()，在同一cat分组内按日期排序后，将当前行的x1、x2与上一行的值进行对比，只要其中任意一列发生变化就筛选出来。

步骤1：创建测试表（可跳过，仅用于验证） #

先把你的测试数据建表插入，方便后续验证：

CREATE TABLE test_table (
    day DATE,
    cat VARCHAR(1),
    x1 VARCHAR(1),
    x2 VARCHAR(5)
);

INSERT INTO test_table VALUES
('2019-10-25', 'A', 'P', 'Open'),
('2019-10-26', 'A', 'P', 'Close'),
('2019-10-27', 'A', 'P', 'Close'),
('2019-10-28', 'A', 'I', 'Close'),
('2019-10-26', 'B', 'P', 'Close'),
('2019-10-27', 'B', 'P', 'Open'),
('2019-10-28', 'B', 'P', 'Open');

步骤2：核心查询语句 #

用CTE结合LAG()函数实现需求：

WITH ranked_data AS (
    SELECT 
        day,
        cat,
        x1,
        x2,
        -- 按cat分组、日期排序，获取上一行的x1和x2
        LAG(x1) OVER (PARTITION BY cat ORDER BY day) AS prev_x1,
        LAG(x2) OVER (PARTITION BY cat ORDER BY day) AS prev_x2
    FROM test_table
)
SELECT day, cat, x1, x2
FROM ranked_data
-- 筛选x1或x2与上一行不同的行，排除分组内第一行（无前置数据）
WHERE (x1 <> prev_x1 OR x2 <> prev_x2)
ORDER BY cat, day;

查询逻辑说明 #

• PARTITION BY cat：确保我们只在同一个分类组内进行行与行的对比
• ORDER BY day：保证按日期顺序获取前一行的数据，对比逻辑才合理
• LAG(x1)/LAG(x2)：分别获取当前行在分组内的上一行对应的x1、x2值
• WHERE条件：只要当前行的x1和上一行不同，或者x2和上一行不同，就选中该行；同时分组的第一行因为没有上一行数据，prev_x1/prev_x2会是NULL，所以不会被选中，完全符合你的期望结果。

执行结果 #

运行上述查询后，会得到你想要的结果：

day        cat x1 x2
---------- --- -- -----
2019-10-26 A   P  Close
2019-10-28 A   I  Close
2019-10-27 B   P  Open

额外补充 #

如果你后续需要包含每个分组的第一行（比如要展示初始状态），只需要调整WHERE条件为：

WHERE (prev_x1 IS NULL OR x1 <> prev_x1 OR x2 <> prev_x2)

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