如何通过SQL生成多日期组织数据汇总的单结果表？

系统内存在transactions表，每当组织参数（如Org Status、Org Type等）发生变更时，会新增一行记录，每行以Org ID和Update DTTM作为唯一标识，记录该时刻组织的所有参数状态。部分组织频繁更新，部分组织仅存历史记录，其最新记录即为当前状态。

当前通过排序取Org ID最新记录的方式可生成单日期的组织状态汇总，但无法一次查询生成多日期汇总并合并为单表。原代码用WHILE循环结合CTE和ROW_NUMBER()得到各日期汇总，但每个日期结果为单独表，无法导入Power Query及Power BI可视化。

解决思路 #

用临时表（或表变量）存储每次循环生成的汇总数据，等所有日期的汇总计算完成后，统一查询临时表就能得到合并后的单表结果，完美适配Power Query/Power BI的导入需求。

修改后的完整代码 #

-- 创建临时表存储所有日期的汇总结果
CREATE TABLE #OrgStatusSummary (
    Active INT,
    Closed INT,
    Dormant INT,
    SystemDate DATE
)

DECLARE @Start date
DECLARE @End date 
DECLARE @counter date
SET @Start = '2020-01-01'  -- 可设置本世纪任意起始日期
SET @End = GETDATE()
SET @counter = @Start

WHILE @counter <= @End
BEGIN
    WITH Moment AS (
        SELECT 
            ORG_ID, 
            STATUS, 
            UPDATE_DTTM, 
            ROW_NUMBER() OVER (
                PARTITION BY ORG_ID  
                ORDER BY UPDATE_DTTM DESC 
            ) AS [ROW NUMBER]
        FROM TRANSACTIONS_TABLE
        WHERE UPDATE_DTTM < @counter
    )
    -- 把当前日期的汇总结果插入临时表
    INSERT INTO #OrgStatusSummary (Active, Closed, Dormant, SystemDate)
    SELECT 
        COUNT(CASE WHEN STATUS = '4' THEN 1 END) AS Active, 
        COUNT(CASE WHEN STATUS = '6' THEN 1 END) AS Closed, 
        COUNT(CASE WHEN STATUS = '5' THEN 1 END) AS Dormant,
        @counter AS SystemDate
    FROM Moment
    WHERE [ROW NUMBER] = 1

    SET @counter = DATEADD(Month, 1, @counter)  -- 切换到下一个月
END

-- 输出合并后的单表结果
SELECT * FROM #OrgStatusSummary
ORDER BY SystemDate

-- 清理临时表（可选，会话结束后会自动删除）
DROP TABLE #OrgStatusSummary

关键改动说明 #

• 新增#OrgStatusSummary临时表，结构和汇总输出字段完全匹配，专门用来存储所有日期的汇总数据
• 将原循环内的SELECT语句改为INSERT INTO，每次循环都把当前日期的汇总结果写入临时表
• 循环结束后直接查询临时表，就能得到所有日期汇总合并成的单表，可直接导入Power Query/Power BI
• 最后可选择删除临时表，避免占用资源（临时表在会话结束后会自动销毁，不删除也不影响）

性能优化版（无循环） #

如果你的SQL Server版本是2016及以上，推荐用递归生成日期序列+窗口函数替代WHILE循环，性能更优，适合数据量大的场景：

DECLARE @Start date = '2020-01-01'
DECLARE @End date = GETDATE()

-- 递归生成需要统计的所有月份第一天
WITH DateSeries AS (
    SELECT @Start AS SystemDate
    UNION ALL
    SELECT DATEADD(Month, 1, SystemDate)
    FROM DateSeries
    WHERE SystemDate < @End
),
-- 获取每个组织在每个统计日期前的最新状态记录
OrgLatestStatus AS (
    SELECT
        ds.SystemDate,
        org.ORG_ID,
        t.STATUS,
        ROW_NUMBER() OVER (
            PARTITION BY ds.SystemDate, org.ORG_ID
            ORDER BY t.UPDATE_DTTM DESC
        ) AS rn
    FROM DateSeries ds
    -- 关联所有存在的组织
    CROSS JOIN (SELECT DISTINCT ORG_ID FROM TRANSACTIONS_TABLE) org
    -- 匹配该组织在统计日期前的所有变更记录
    LEFT JOIN TRANSACTIONS_TABLE t
        ON org.ORG_ID = t.ORG_ID
        AND t.UPDATE_DTTM < ds.SystemDate
)
-- 按日期汇总各状态数量
SELECT
    SystemDate,
    COUNT(CASE WHEN STATUS = '4' THEN 1 END) AS Active,
    COUNT(CASE WHEN STATUS = '6' THEN 1 END) AS Closed,
    COUNT(CASE WHEN STATUS = '5' THEN 1 END) AS Dormant
FROM OrgLatestStatus
WHERE rn = 1  -- 只取每个组织的最新记录
GROUP BY SystemDate
ORDER BY SystemDate
OPTION (MAXRECURSION 0)  -- 解除递归次数限制，支持生成多年的日期序列

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