如何用TSQL筛选/更新间隔超30天的门禁卡记录

需求说明 #

从badge-records（门禁卡记录表）中筛选或更新满足以下条件的记录（仅针对相同门禁卡号的记录进行对比）：

• 该门禁卡的首次扫描记录
• 同一张门禁卡的本次扫描时间与上一次扫描时间间隔超过30天的记录

示例数据及筛选结果 #

TSQL解决方案 #

1. 筛选符合条件的记录 #

使用LAG()窗口函数获取同一张门禁卡的上一次扫描时间，再判断是否满足筛选条件：

SELECT 
    TimeStamp,
    Badge,
    CASE 
        WHEN prev_scan_time IS NULL THEN '首次扫描'
        WHEN DATEDIFF(DAY, prev_scan_time, TimeStamp) > 30 THEN '与上一次扫描间隔超30天'
    END AS 筛选原因
FROM (
    SELECT 
        TimeStamp,
        Badge,
        -- 按门禁卡号分组，按扫描时间排序，获取上一次扫描时间
        LAG(TimeStamp) OVER (PARTITION BY Badge ORDER BY TimeStamp) AS prev_scan_time
    FROM badge-records
) AS sub_query
WHERE 
    -- 首次扫描记录
    prev_scan_time IS NULL 
    -- 与上一次扫描间隔超过30天
    OR DATEDIFF(DAY, prev_scan_time, TimeStamp) > 30;

2. 更新符合条件的记录 #

如果需要给符合条件的记录添加标记（比如用IsValid列标记为1），可以用CTE结合窗口函数实现：

WITH badge_cte AS (
    SELECT 
        TimeStamp,
        Badge,
        IsValid, -- 假设表中已有该标记列
        LAG(TimeStamp) OVER (PARTITION BY Badge ORDER BY TimeStamp) AS prev_scan_time
    FROM badge-records
)
UPDATE badge_cte
SET IsValid = 1
WHERE 
    prev_scan_time IS NULL 
    OR DATEDIFF(DAY, prev_scan_time, TimeStamp) > 30;

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