如何合并多段含UNION的SQL计算并写入单行汇总表？

1. 创建汇总表 #

先创建符合需求的汇总表，包含Name列、8个统计列以及LoadDate列：

CREATE TABLE dbo.DailySummary (
    Name NVARCHAR(100) NOT NULL,
    current_day INT NOT NULL,
    current_week INT NOT NULL,
    current_month INT NOT NULL,
    -- 请根据你的8段计算逻辑补充剩余5个统计列，比如current_quarter、current_year等
    LoadDate DATE NOT NULL
);

2. 编写存储过程 #

存储过程需完成两个核心操作：清空原有数据，将8段统计逻辑的结果合并为一行插入汇总表。

基础版（直接复用原有统计逻辑）

如果8段统计逻辑差异较大，可直接将每个统计作为独立子查询：

CREATE PROCEDURE dbo.LoadDailySummary
AS
BEGIN
    SET NOCOUNT ON;

    -- 清空表中原有数据
    DELETE FROM dbo.DailySummary;

    -- 插入合并后的统计结果
    INSERT INTO dbo.DailySummary (
        Name,
        current_day,
        current_week,
        current_month,
        -- 补充剩余5个统计列名
        LoadDate
    )
    SELECT
        -- 假设所有统计结果的Name一致，取第一个即可；若Name固定可直接写死，比如'业务总览'
        (SELECT TOP 1 Name FROM (
            SELECT Name FROM [Table1] WHERE CONVERT(DATE, dte_Uploaded) = CONVERT(DATE, CURRENT_TIMESTAMP)
            UNION ALL
            SELECT Name FROM [Table2] WHERE CONVERT(DATE, dte_Uploaded) = CONVERT(DATE, CURRENT_TIMESTAMP)
        ) x) AS Name,
        -- 当日统计
        (SELECT COUNT(CAST(id AS INT)) FROM (
            SELECT id FROM [Table1] WHERE CONVERT(DATE, dte_Uploaded) = CONVERT(DATE, CURRENT_TIMESTAMP)
            UNION ALL
            SELECT id FROM [Table2] WHERE CONVERT(DATE, dte_Uploaded) = CONVERT(DATE, CURRENT_TIMESTAMP)
        ) x) AS current_day,
        -- 当周统计
        (SELECT COUNT(CAST(id AS INT)) FROM (
            SELECT id FROM [Table1] WHERE DATEDIFF(ww, dte_Uploaded, GETDATE()) = 0
            UNION ALL
            SELECT id FROM [Table2] WHERE DATEDIFF(ww, dte_Uploaded, GETDATE()) = 0
        ) x) AS current_week,
        -- 当月统计
        (SELECT COUNT(CAST(id AS INT)) FROM (
            SELECT id FROM [Table1] WHERE DATEDIFF(m, dte_Uploaded, GETDATE()) = 0
            UNION ALL
            SELECT id FROM [Table2] WHERE DATEDIFF(m, dte_Uploaded, GETDATE()) = 0
        ) x) AS current_month,
        -- 请在这里补充剩余5段统计逻辑的子查询
        CONVERT(DATE, CURRENT_TIMESTAMP) AS LoadDate;
END
GO

优化版（减少重复扫描表，提升性能）

如果Table1和Table2数据量较大，多次扫描会影响性能，可先用CTE合并两张表的全量数据，再通过条件统计完成所有计算：

CREATE PROCEDURE dbo.LoadDailySummary
AS
BEGIN
    SET NOCOUNT ON;
    -- 若需要周一作为周起始日，取消下面注释
    -- SET DATEFIRST 1;

    -- 清空表中原有数据
    DELETE FROM dbo.DailySummary;

    -- CTE合并Table1和Table2的基础数据，仅扫描一次两张表
    WITH CombinedData AS (
        SELECT 
            Name,
            CAST(id AS INT) AS id,
            dte_Uploaded
        FROM [Table1]
        UNION ALL
        SELECT 
            Name,
            CAST(id AS INT) AS id,
            dte_Uploaded
        FROM [Table2]
    )
    INSERT INTO dbo.DailySummary (
        Name,
        current_day,
        current_week,
        current_month,
        -- 补充剩余5个统计列名
        LoadDate
    )
    SELECT
        -- 取唯一的Name（假设所有数据Name统一）
        (SELECT TOP 1 Name FROM CombinedData) AS Name,
        -- 当日统计
        COUNT(CASE WHEN CONVERT(DATE, dte_Uploaded) = CONVERT(DATE, CURRENT_TIMESTAMP) THEN id END) AS current_day,
        -- 当周统计
        COUNT(CASE WHEN DATEDIFF(ww, dte_Uploaded, GETDATE()) = 0 THEN id END) AS current_week,
        -- 当月统计
        COUNT(CASE WHEN DATEDIFF(m, dte_Uploaded, GETDATE()) = 0 THEN id END) AS current_month,
        -- 补充剩余5段统计逻辑，示例：
        -- COUNT(CASE WHEN DATEDIFF(qq, dte_Uploaded, GETDATE()) = 0 THEN id END) AS current_quarter,
        -- COUNT(CASE WHEN CONVERT(DATE, dte_Uploaded) = DATEADD(DAY, -1, CONVERT(DATE, CURRENT_TIMESTAMP)) THEN id END) AS previous_day,
        CONVERT(DATE, CURRENT_TIMESTAMP) AS LoadDate
    FROM CombinedData;
END
GO

3. 执行与调度 #

• 手动执行存储过程：

EXEC dbo.LoadDailySummary;

• 每日自动执行：通过SQL Server代理创建定时作业，设置每日指定时间执行该存储过程，实现自动清空重加载。

注意事项 #

• 若Name是固定业务标识，可直接在SELECT中写死（比如'业务统计' AS Name），避免子查询取值的开销。
• DATEDIFF(ww, ...)的周范围默认以周日为第一天，若业务需要周一为起始日，需在存储过程开头添加SET DATEFIRST 1;。
• 确保Table1和Table2的id列可以转换为INT类型，若存在转换失败的情况，可添加TRY_CAST替代CAST避免报错。

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