TSQL如何基于时间字段获取指定FILOID的前序及后续关联记录

实现逻辑 #

• 基于原有查询逻辑，给所有符合员工、客户条件的记录按StartTransactionTimeUTC升序分配唯一行号
• 定位到传入的目标FILOID对应的行号，将筛选条件设为行号 >= 目标行号-1，即可同时拿到目标的前一条、目标本身及所有后续记录
• 保留原有NextFilo、LastFilo的取值规则不变

完整可执行代码 #

-- 声明传入参数，示例目标FILOID为6，可按需修改
DECLARE @TargetFILOID INT = 6;
DECLARE @TargetEmployeeID INT = 15049;
DECLARE @TargetClientID INT = 306;

WITH AllFILORecords AS (
    SELECT 
        fs.FILOID CurrentFilo,
        ISNULL(LEAD(af.FILOID) OVER (ORDER BY StartTransactionTimeUTC), fs.Next_FILOID) NextFilo,
        ISNULL(LAG(af.FILOID) OVER (ORDER BY StartTransactionTimeUTC), fs.Previous_FILOID) LastFilo,
        -- 按时间排序分配行号
        ROW_NUMBER() OVER (ORDER BY StartTransactionTimeUTC) RowNum
    FROM 
        [DataWarehouseCore].[DWC_FILOSummary] fs 
    JOIN 
        [AttendanceCore].[AC_FILO] af ON af.FILOID = fs.FILOID
    WHERE 
        fs.Employee_ID = @TargetEmployeeID
        AND Client_ID = @TargetClientID
)
SELECT CurrentFilo, NextFilo, LastFilo
FROM AllFILORecords
WHERE RowNum >= (SELECT RowNum FROM AllFILORecords WHERE CurrentFilo = @TargetFILOID) - 1
ORDER BY RowNum ASC

效果验证 #

当传入@TargetFILOID = 6时，查询输出和预期结果完全一致：

CurrentFilo NextFilo LastFilo
-----------------------------
3           6        2
6           7        3
7           1        6
1           NULL     7

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