如何不使用递归实现SQL聚合数据拆分并保存为视图？

我们有一张存储**聚合（rolled up）格式数据的表，现有所有查询和视图都基于非聚合（unrolled up）**层级运行，关联该表查询单值数据时存在明显问题。

曾尝试用递归CTE实现数据拆分，但服务器仅允许100次递归，修改限制需用OPTION语句，而访问数据的业务系统不支持此设置，导致无法将该逻辑保存为视图。

请问有没有其他实现数据拆分效果且可保存为视图的方法？

数据示例 #

原始表数据 #

+----+------------+------------+------+
| id | start date |  end date  | days |
+----+------------+------------+------+
|  1 | 2023-01-01 | 2023-01-05 |    5 |
|  1 | 2023-01-08 | 2023-01-10 |    3 |
|  2 | 2023-12-30 | 2023-12-31 |    2 |
|  3 | 2023-02-05 | 2023-02-07 |    3 |
+----+------------+------------+------+

期望输出 #

+----+------------+------+
| id |    date    | days |
+----+------------+------+
|  1 | 2023-01-01 |    1 |
|  1 | 2023-01-02 |    1 |
|  1 | 2023-01-03 |    1 |
|  1 | 2023-01-04 |    1 |
|  1 | 2023-01-05 |    1 |
|  1 | 2023-01-08 |    1 |
|  1 | 2023-01-09 |    1 |
|  1 | 2023-01-10 |    1 |
|  2 | 2023-12-30 |    1 |
|  2 | 2023-12-31 |    1 |
|  3 | 2023-02-05 |    1 |
|  3 | 2023-02-06 |    1 |
|  3 | 2023-02-07 |    1 |
+----+------------+------+

递归CTE受限的情况下，数字辅助表是更可靠的替代方案——它不需要递归逻辑，也不需要额外的OPTION设置，完全可以封装成视图直接供业务系统调用。

实现步骤 #

1. 生成数字序列（嵌入视图内） #

利用系统表生成足够覆盖最大日期跨度的数字序列（示例中取TOP 1000，可根据实际业务调整）：

SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2

注：sys.all_columns是数据库自带的系统表，数据量足够生成大序列；也可以用其他系统表或自定义固定数字表替代。

2. 创建拆分视图 #

将原始表与数字序列关联，拆分出每一天的记录：

CREATE VIEW UnrolledDateView AS
SELECT 
    t.id,
    DATEADD(DAY, tl.n - 1, t.[start date]) AS date,
    1 AS days
FROM 
    YourOriginalTable t
JOIN 
    (
        SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
        FROM sys.all_columns ac1
        CROSS JOIN sys.all_columns ac2
    ) tl ON tl.n <= t.days

关键说明 #

• 视图无需特殊配置，业务系统可直接查询调用
• 数字序列的长度（TOP 1000）需根据业务中最大的days值调整，确保覆盖所有可能的日期跨度
• 关联条件tl.n <= t.days保证每条聚合记录拆分出对应天数的行，拆分后的days固定为1，完全匹配期望输出

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