SQLServer 默认递归上限问题及三种可靠解决方案_MsSqlserver

我在 sql server 里用递归 cte 生成时间序列时，触发了默认递归上限：

the maximum recursion 100 has been exhausted...

这正是 sql server 的典型报错（默认 maxrecursion = 100）。

下面给你三种可靠解决方案，按推荐顺序排列：

✅ 方案 a：加大递归上限或无限制（最快改法）

在你的查询最后加上 option (maxrecursion n)：

指定一个足够大的 n（如 100000），或
用 0 表示不限制（要确保查询本身不会死循环）。

-- 你的原始递归cte查询
with ts as (
  select cast('2025-10-01t00:00:00' as datetime2) as bucket
  union all
  select dateadd(minute, 10, bucket)
  from ts
  where bucket < cast('2025-10-02t00:00:00' as datetime2)
)
select *
from ts
option (maxrecursion 0);  -- 0 = 不限制（生产慎用，需确保 where 正确）

适合临时脚本或时间跨度不算特别大的场景。
如果你的时间间隔很小、跨度很长（例如 1 分钟跨数月），递归会很深，不建议长期依赖。

✅ 方案 b：改用tally/numbers 表（生产推荐，稳定高效）

不用递归，直接用连续整数生成时间点，性能最佳。

1) 一次性建一个 numbers 表（建议持久化）

-- 建表：包含从 0 开始的连续整数
create table dbo.numbers (n int not null primary key);
-- 生成足够多的行（示例：生成 1,000,000 行）
;with e1(n) as (select 1 union all select 1),
e2 as (select 1 from e1 a cross join e1 b),        -- 4
e4 as (select 1 from e2 a cross join e2 b),        -- 16
e8 as (select 1 from e4 a cross join e4 b),        -- 256
e16 as (select 1 from e8 a cross join e8 b),       -- 65,536
e32 as (select 1 from e16 a cross join e16 b)      -- ~4b（谨慎）
insert into dbo.numbers(n)
select top (1000000) row_number() over (order by (select null)) - 1
from e32;

只需建一次，后续所有“时间补齐”都复用它。

2) 用 numbers 表生成时间序列并补齐

declare @start datetime2 = '2025-10-01t00:00:00';
declare @end   datetime2 = '2025-10-02t00:00:00';
declare @step_min int = 5; -- 间隔：5分钟
with ts as (
  select dateadd(minute, n * @step_min, @start) as bucket
  from dbo.numbers
  where dateadd(minute, n * @step_min, @start) < @end  -- 通常用 [start, end)
),
agg as (
  -- 将事实表事件对齐到 5 分钟桶
  select dateadd(minute, datediff(minute, 0, event_time) / @step_min * @step_min, 0) as bucket_5m,
         count(*) as c
  from dbo.eventlog
  where event_time >= @start and event_time < @end
  group by dateadd(minute, datediff(minute, 0, event_time) / @step_min * @step_min, 0)
)
select ts.bucket,
       isnull(agg.c, 0) as event_count
from ts
left join agg on ts.bucket = agg.bucket_5m
order by ts.bucket;

优点

没有递归深度限制
可预估生成行数：行数 ≈ ceiling(datediff(minute, @start, @end) / @step_min)
最适合生产环境与大跨度时间序列

✅ 方案 c：不建持久表，临时生成 tally（适合脚本/一次性查询）

用系统表 + row_number() 临时造数：

declare @start datetime2 = '2025-10-01t00:00:00';
declare @end   datetime2 = '2025-10-02t00:00:00';
declare @step_min int = 15;
;with n as (
  select top (1000000) row_number() over (order by (select null)) - 1 as n
  from sys.all_objects a cross join sys.all_objects b
),
ts as (
  select dateadd(minute, n * @step_min, @start) as bucket
  from n
  where dateadd(minute, n * @step_min, @start) < @end
),
agg as (
  select dateadd(minute, datediff(minute, 0, event_time) / @step_min * @step_min, 0) as bucket_15m,
         count(*) as c
  from dbo.eventlog
  where event_time >= @start and event_time < @end
  group by dateadd(minute, datediff(minute, 0, event_time) / @step_min * @step_min, 0)
)
select ts.bucket,
       isnull(agg.c, 0) as event_count
from ts
left join agg on ts.bucket = agg.bucket_15m
order by ts.bucket;

把 top (1000000) 调整到能覆盖你的区间即可。

常见细节与坑

区间边界：推荐用 [start, end)（即 < @end），避免终点重复。
分箱对齐：dateadd(minute, datediff(minute, 0, event_time) / step * step, 0) 是 sql server 经典对齐写法。
时区：全部用同一时区（utc 或本地）进行聚合；需要展示时再做转换。
性能：给 event_time 建索引/分区；先裁剪再聚合；numbers 表持久化优于临时递表或递归。
极大跨度：优先用“按天序列 + 维表/应用层展开”，不要用深递归。

到此这篇关于sqlserver 默认递归上限问题的文章就介绍到这了,更多相关sqlserver递归上限内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！