SQL中LAG函数返回结果不符合预期的问题及需求实现

数据表结构与数据 #

创建表语句 #

CREATE TABLE [dbo].[testtable]
(
    [EmpID] [int] NOT NULL,
    [Status] [nvarchar](5) NOT NULL,
    [History] [nvarchar](5) NOT NULL,
    [EntryDate] DateTime NOT NULL
 )

插入测试数据 #

INSERT INTO [dbo].[testtable] ([EmpID], [Status], [History], EntryDate)
VALUES (1, 'N', 'OLD', '2022-03-01 13:00'),
       (1, 'C', 'OLD', '2022-03-01 16:00'),
       (1, 'C', 'OLD', '2022-04-01 16:00'),
       (1, 'T', 'CUR', '2022-05-01 08:00'),
       (2, 'N', 'OLD', '2022-04-01 16:00'),
       (2, 'R', 'OLD', '2022-05-01 07:00'),
       (2, 'F', 'OLD', '2022-06-01 15:00'),
       (2, 'S', 'CUR', '2022-07-01 14:00'),
       (3, 'N', 'CUR', '2022-03-01 17:00'),
       (4, 'N', 'OLD', '2022-05-01 16:00'),
       (4, 'F', 'OLD', '2022-06-01 11:00'),
       (4, 'G', 'OLD', '2022-07-01 20:00'),
       (4, 'G', 'CUR', '2022-08-01 19:00')

原查询存在的问题 #

原使用LAG函数的查询语句：

SELECT 
    EMPID, FromSt, ToSt, History
FROM 
    (SELECT 
         EMPID,
         ISNULL(LAG(Status) OVER (ORDER BY EMPID ASC), 'N') AS FromSt,
         Status AS ToSt,
         History
     FROM  
         [dbo].[testtable]
     -- WHERE History = 'CUR'
    ) InnerQuery
WHERE 
    FromSt <> ToSt

核心问题：LAG函数未按EmpID分区，导致不同EmpID的第一条记录会取前一个EmpID的最后状态，且无法过滤同EmpID内的连续重复状态。原查询输出：

EMPID    FromSt  ToSt    
       --------------------- 
       1         N      C         
       1         C      T         
       2         T      N        
       2         N      R         
       2         R      F         
       2         F      S         
       3         S      N         
       4         N      F         
       4         F      G        

已知规则：每个EmpID的最早记录Status为'N'、History为'OLD'，最新记录History为'CUR'。

场景1：展示同一EmpID内的所有状态变更 #

需求：查询所有记录时，仅展示同一EmpID内的状态变更，排除连续重复的状态记录，期望输出：

EmpID  FromSt  ToSt   
       ------------------- 
        1       N       C     
        1       C       T     
        2       N       R     
        2       R       F    
        2       F       S  
        4       N       G  

解决SQL：

SELECT 
    EmpID, FromSt, ToSt
FROM (
    SELECT 
        EmpID,
        -- 按EmpID分区，仅在同一员工内取上一条状态
        LAG(Status) OVER (PARTITION BY EmpID ORDER BY EntryDate ASC) AS FromSt,
        Status AS ToSt,
        -- 标记连续重复的状态
        CASE WHEN LAG(Status) OVER (PARTITION BY EmpID ORDER BY EntryDate ASC) = Status THEN 1 ELSE 0 END AS IsDuplicate
    FROM [dbo].[testtable]
) AS InnerQuery
WHERE 
    IsDuplicate = 0 
    AND FromSt IS NOT NULL -- 排除每个员工的初始状态记录

说明：

1. 给LAG函数添加PARTITION BY EmpID，确保状态变更仅在同一员工范围内计算，避免跨员工干扰。
2. 通过IsDuplicate标记过滤连续重复的状态（如EmpID1的两条'C'状态，仅保留第一次变更记录）。
3. 过滤FromSt IS NOT NULL，排除每个员工的初始状态（无前置变更的记录）。

场景2：筛选History='CUR'记录对应的历史状态变更 #

需求：仅查询History='CUR'的记录时，筛选出与当前状态不同的最新历史状态变更，无变更的EmpID3需排除，期望输出：

EmpID    FromSt    ToSt   
       ----------------------- 
       1        C          T   
       2        F          S
       2        R          S
       4        F          G

解决SQL：

WITH EmpCurrentStatus AS (
    -- 获取每个员工的当前状态（History='CUR'的记录）
    SELECT 
        EmpID, 
        Status AS CurrentStatus
    FROM [dbo].[testtable]
    WHERE History = 'CUR'
),
EmpDistinctHistory AS (
    -- 提取每个员工内与当前状态不同的历史状态，并保留每个状态的最新记录
    SELECT 
        t.EmpID,
        t.Status,
        ROW_NUMBER() OVER (PARTITION BY t.EmpID, t.Status ORDER BY t.EntryDate DESC) AS rn
    FROM [dbo].[testtable] t
    JOIN EmpCurrentStatus cs ON t.EmpID = cs.EmpID
    WHERE t.History = 'OLD'
      AND t.Status <> cs.CurrentStatus -- 排除与当前状态相同的历史记录
)
SELECT 
    edh.EmpID,
    edh.Status AS FromSt,
    cs.CurrentStatus AS ToSt
FROM EmpDistinctHistory edh
JOIN EmpCurrentStatus cs ON edh.EmpID = cs.EmpID
WHERE edh.rn = 1 -- 每个历史状态仅保留最新一条
ORDER BY edh.EmpID, edh.Status

说明：

1. 用CTEEmpCurrentStatus获取所有员工的当前状态。
2. 用CTEEmpDistinctHistory筛选出与当前状态不同的历史记录，通过ROW_NUMBER()按状态分区取最新一条，避免重复状态。
3. 关联两个CTE输出结果，自动排除无变更的EmpID3（其无符合条件的历史记录）。

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