Access查询：按发票分组移除连续重复状态记录

原始数据（MyTable表） #

期望结果 #

需求说明 #

按Invoice（发票）分组，若同一状态连续重复出现，仅保留该组连续重复状态中时间最早的记录。需注意状态可能非连续重复，因此不能直接按Status分组取最早记录。

SQL解决方案 #

使用窗口函数标记连续状态分组，再聚合取每组最早记录，兼容多数支持窗口函数的数据库（如MySQL 8.0+、PostgreSQL、SQL Server等）：

WITH status_groups AS (
    SELECT 
        Invoice,
        Status,
        StatusDateTime,
        -- 生成连续状态的分组ID：状态变化时累加1
        SUM(CASE WHEN prev_status != Status OR prev_status IS NULL THEN 1 ELSE 0 END) 
            OVER (PARTITION BY Invoice ORDER BY StatusDateTime) AS group_id
    FROM (
        SELECT 
            Invoice,
            Status,
            StatusDateTime,
            -- 获取同一发票下前一条记录的状态
            LAG(Status) OVER (PARTITION BY Invoice ORDER BY StatusDateTime) AS prev_status
        FROM MyTable
    ) t
)
SELECT 
    Invoice,
    Status,
    MIN(StatusDateTime) AS StatusDateTime
FROM status_groups
GROUP BY Invoice, Status, group_id
ORDER BY Invoice, StatusDateTime;

逻辑说明 #

1. 内层子查询通过LAG()窗口函数，获取同一发票下按时间排序后的前一条记录状态，用于判断当前状态是否和上一条连续重复。
2. 中间CTE使用SUM()窗口函数，每当状态发生变化（包括分组内第一条记录）时累加1，将连续相同的状态归为同一个group_id。
3. 最后按发票、状态和分组ID聚合，取每个分组的最早时间，得到去重后的结果。

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