如何多次运行含CTE的SQL查询 替换@ToDate参数获取多日期客户报告

最优实现方案：内联表值函数关联日历表（完全避免循环，性能最优） #

这种方案是SQL场景下处理多快照批量查询的首选，内联表值函数会被SQL优化器直接展开到整体执行计划中，不会产生重复执行的额外开销，刚好匹配你已有日历表的前提。

实现步骤 #

1. 将你现有查询封装为入参为@ToDate的内联表值函数：

CREATE FUNCTION dbo.GetCustomerSnapshot (@ToDate DATE)
RETURNS TABLE
AS RETURN
(
    -- 原查询逻辑直接复用，仅删除@ToDate的变量声明即可
    WITH 
    cte1 AS (
        SELECT * FROM table1 
        WHERE table1.start_date <= @ToDate 
        AND (table1.end_date > @ToDate OR table1.end_date IS NULL)
    ),
    cte2 AS (
        SELECT * FROM table2 
        WHERE table2.start_date <= @ToDate 
        AND (table2.end_date > @ToDate OR table2.end_date IS NULL)
    )
    SELECT 
        @ToDate AS RunDate, -- 显式返回当前快照日期
        cte1.CustomerID,
        cte1.DOB,
        cte2.Category,
        -- 其余你需要输出的字段
    FROM cte1 
    LEFT JOIN cte2 ON cte2.id = cte1.id
    WHERE -- 你原有查询的过滤条件
);

2. 用日历表和函数交叉应用，一次性取到所有日期的快照结果：

SELECT s.*
FROM 你的日历表 cal
CROSS APPLY dbo.GetCustomerSnapshot(cal.日期字段) s
WHERE 
    -- 过滤规则：包含指定基准日期 + 过去n个周一
    (cal.日期字段 = '2021-08-30' 
    OR (cal.星期标识 = '周一' AND cal.日期字段 < '2021-08-30'))
    AND cal.日期字段 >= DATEADD(WEEK, -@n, '2021-08-30') -- 替换@n为你需要的周数
ORDER BY s.RunDate DESC, s.CustomerID;

备选方案：循环实现（仅当你没有创建函数权限时使用） #

如果环境限制无法创建自定义函数，可以用游标循环实现，逻辑更直观但性能低于上述方案。

-- 1. 先创建临时表存储所有快照结果，字段和你查询输出完全匹配
CREATE TABLE #AllResults (
    RunDate DATE,
    CustomerID INT,
    DOB DATE,
    Category VARCHAR(50),
    -- 其余对应字段
);

-- 2. 声明变量和游标加载需要执行的所有快照日期
DECLARE @ToDate DATE, @n INT = 10; -- 替换@n为你需要的周数
DECLARE date_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT 日期字段 FROM 你的日历表
WHERE 
    (日期字段 = '2021-08-30' 
    OR (星期标识 = '周一' AND 日期字段 < '2021-08-30'))
    AND 日期字段 >= DATEADD(WEEK, -@n, '2021-08-30')
ORDER BY 日期字段 DESC;

OPEN date_cursor;
FETCH NEXT FROM date_cursor INTO @ToDate;

-- 3. 循环执行查询插入结果
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO #AllResults(RunDate, CustomerID, DOB, Category, 其余字段)
    -- 直接复用你原有的查询逻辑
    WITH 
    cte1 AS (
        SELECT * FROM table1 
        WHERE table1.start_date <= @ToDate 
        AND (table1.end_date > @ToDate OR table1.end_date IS NULL)
    ),
    cte2 AS (
        SELECT * FROM table2 
        WHERE table2.start_date <= @ToDate 
        AND (table2.end_date > @ToDate OR table2.end_date IS NULL)
    )
    SELECT 
        @ToDate AS RunDate,
        cte1.CustomerID,
        cte1.DOB,
        cte2.Category,
        -- 其余字段
    FROM cte1 
    LEFT JOIN cte2 ON cte2.id = cte1.id
    WHERE -- 原有过滤条件
    ;

    FETCH NEXT FROM date_cursor INTO @ToDate;
END

-- 4. 释放资源、输出结果
CLOSE date_cursor;
DEALLOCATE date_cursor;

SELECT * FROM #AllResults ORDER BY RunDate DESC, CustomerID;
DROP TABLE #AllResults;

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