在 SQL Server 中，若通过递归 CTE（公用表表达式）生成时间序列数据，最常见的“障碍”便是默认的递归层级上限——尽管逻辑本身毫无问题，执行时却直接抛出以下错误：
The maximum recursion 100 has been exhausted...
确实，SQL Server 默认只允许递归 100 层（即 MAXRECURSION = 100）。一旦时间跨度稍大或者粒度更细，这条限制便会立即阻断查询的执行。
下文提供三种可靠解决方案，按推荐优先级排序，覆盖从快速应急到生产环境的不同需求。
✅ 方案 A：调整递归上限或取消限制（最快捷的方法）
如果你的场景属于临时脚本，且时间跨度并非特别夸张，最简便的方式是在查询末尾添加 OPTION (MAXRECURSION 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 条件正确）
适用于临时脚本或时间跨度适中的场景。
如果时间间隔极小、跨度很长（例如每分钟跨越多个月），递归深度会非常大，不建议长期依赖此方案。
✅ 方案 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 序列（适用脚本或一次性查询）
如果不想持久化 Numbers 表，可以借助系统视图结合 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 表比递归或临时递表性能更优。 - 极大跨度：优先采用“按天序列 + 维度表/应用层展开”的方式，避免深度递归。
