MS SQL中从锚点日期满30天重置ROW_NUMBER的实现方法
你的需求属于典型的递归区间分组场景,MS SQL v14(即SQL Server 2017)可通过递归CTE+窗口函数的组合方案大幅简化实现,无需创建多个临时表和手动更新标记位,完整实现代码如下:
WITH sorted_data AS ( -- 按用户分组、日期升序排序,生成全局行号用于逐行遍历 SELECT name_test, date_test, ROW_NUMBER() OVER(PARTITION BY name_test ORDER BY date_test) AS rn FROM test_D ), recur_calc AS ( -- 递归锚点:取每个用户的第一条记录作为初始锚点,行号从1开始 SELECT name_test, date_test, rn, date_test AS anchor_date, 1 AS RowNumber FROM sorted_data WHERE rn = 1 UNION ALL -- 递归遍历后续所有记录 SELECT curr.name_test, curr.date_test, curr.rn, -- 距离上一个锚点满30天则更新锚点为当前日期,否则沿用原有锚点 CASE WHEN DATEDIFF(DAY, prev.anchor_date, curr.date_test) >= 30 THEN curr.date_test ELSE prev.anchor_date END AS anchor_date, -- 距离上一个锚点满30天则重置行号为1,否则行号累加 CASE WHEN DATEDIFF(DAY, prev.anchor_date, curr.date_test) >= 30 THEN 1 ELSE prev.RowNumber + 1 END AS RowNumber FROM sorted_data curr INNER JOIN recur_calc prev ON curr.name_test = prev.name_test AND curr.rn = prev.rn + 1 ) -- 输出最终结果,顺序和原表排序一致 SELECT name_test, date_test, RowNumber FROM recur_calc ORDER BY name_test, date_test -- 若单个用户的记录数超过100条,需添加以下参数取消默认递归深度限制 OPTION (MAXRECURSION 0);
上述代码完全符合你要求的判定规则:仅当记录距离上一个锚点日期满30天时才重置计数、更新锚点,输出结果与你给出的预期完全匹配。
内容的提问来源于stack exchange,提问作者Jason
相关产品推荐
相关产品推荐

