SQL Server中如何让LEAD函数仅对比相同ActivityTypeID的记录

你的问题核心在于当前LEAD函数的分区逻辑只基于StatementCode，导致不同ActivityTypeID的记录被归到同一组，所以LEAD会取出其他类型的记录。要让LEAD仅对比相同ActivityTypeID的记录，只需要在PARTITION BY中同时包含ActivityTypeID即可。

修改后的查询语句 #

SELECT 
    a.ActivityTypeID,
    acs.ActivityStatementID,
    LEAD(acs.ActivityStatementID) OVER (
        PARTITION BY StatementCode, a.ActivityTypeID  -- 新增ActivityTypeID作为分区条件
        ORDER BY a.ActualDateTime DESC
    ) PreviousStatementID,
    acs.Value as CurrentValue,
    LEAD(acs.Value) OVER (
        PARTITION BY StatementCode, a.ActivityTypeID  -- 同样新增ActivityTypeID
        ORDER BY a.ActualDateTime DESC
    ) as Prev 
FROM ActivityStatement acs 
INNER JOIN Activity a on a.ActivityID = acs.ActivityID 
WHERE 
    a.CustomerID = 128077 
    and a.TenantID = 19 
    and a.ActualDateTime IS NOT NULL

原理说明 #

PARTITION BY的作用是将整个数据集拆分为多个独立的分组，LEAD函数只会在每个分组内部按照指定的排序规则查找下一条记录。

在你原来的查询中，仅用StatementCode分区，所以即使ActivityTypeID不同，只要StatementCode一致就会被分到同一组。加入a.ActivityTypeID后，只有当StatementCode和ActivityTypeID都相同时，记录才会被归为同一组，这样LEAD就只会在相同类型的记录中获取前一条（按ActualDateTime降序排列的下一条，也就是更早的那条同类型记录）。

验证效果 #

修改后，你提到的ActivityStatementID=849433（ActivityTypeID=397）和849609（ActivityTypeID=396）会被分到不同的分组，所以LEAD不会再把849609作为849433的PreviousStatementID，而是只会在ActivityTypeID=397的记录中查找符合条件的前一条记录。

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