SQL Server：如何每小时执行查询并全量更新结果表？

嘿，你的思路完全没问题——用SQL Server Agent定时执行存储过程来刷新结果表，这确实是这类场景下的标准最优方案之一，完美匹配你“每小时全量替换数据”的需求。不过咱们可以从两个方向再优化下：一是让存储过程的执行更丝滑，二是把你那慢查询的速度提上来。

• 存储过程里用TRUNCATE TABLE代替DELETE清空表：TRUNCATE是DDL操作，锁表时间更短，且不会生成大量日志，比DELETE高效得多
• 考虑用临时表+替换减少阻塞：先把查询结果插入临时表，再用ALTER TABLE SWITCH（如果是分区表）或者直接替换原表（无外键约束时），这样后端查询不会因为清空/插入过程被卡住
• 作业配置里加上失败重试+邮件告警：万一某次执行失败，能及时发现并自动重试，避免数据过期

看了你的CTE，核心问题是对acKey的处理冗余、两次扫描tHE_Move表，还有日期函数的使用可能导致索引失效。咱们一步步改进：

1. 简化acKey的条件判断 #

RIGHT(LEFT(M.acKey,5),3)等价于SUBSTRING(M.acKey,3,3)（取第3位开始的3个字符），把所有匹配值放到IN子句里，可读性和优化器处理效率都会提升：

-- 销售类acKey匹配规则
SUBSTRING(M.acKey,3,3) IN ('300','305','319','380','355','360','3X1','395')
-- 零售类acKey匹配规则
SUBSTRING(M.acKey,3,3) IN ('321','322','323','324','325','326','327','328','329','331','332','333','334','335','336','337','338','339','341','342','343','344','345','346','347','348','349','352','353')

2. 合并CTE，避免重复扫描表 #

原来的两个CTE都需要扫描tHE_Move，咱们可以一次扫描就计算出销售和零售数据，减少IO开销：

WITH combinedCTE AS (
    SELECT 
        CONCAT(YEAR(m.addate), '-', FORMAT(m.addate, 'MM')) AS ym,
        YEAR(m.addate) AS y,
        FORMAT(m.addate, 'MM') AS m,
        -- 按acKey分类统计销售额
        CASE WHEN SUBSTRING(M.acKey,3,3) IN ('300','305','319','380','355','360','3X1','395') THEN SUM(M.anvalue) END AS salesRev,
        CASE WHEN SUBSTRING(M.acKey,3,3) IN ('321','322','323','324','325','326','327','328','329','331','332','333','334','335','336','337','338','339','341','342','343','344','345','346','347','348','349','352','353') THEN SUM(M.anvalue) END AS retailRev
    FROM tHE_Move m
    WHERE 
        SUBSTRING(M.acKey,3,3) IN ('300','305','319','380','355','360','3X1','395','321','322','323','324','325','326','327','328','329','331','332','333','334','335','336','337','338','339','341','342','343','344','345','346','347','348','349','352','353')
        AND m.adDate BETWEEN '2014-01-01' AND '2030-01-01' -- 用标准日期格式，避免转换错误
    GROUP BY CONCAT(YEAR(m.addate), '-', FORMAT(m.addate, 'MM')), YEAR(m.addate), FORMAT(m.addate, 'MM')
),
aggregatedCTE AS (
    SELECT 
        ym,
        y,
        m,
        COALESCE(SUM(salesRev), 0) AS salesRev,
        COALESCE(SUM(retailRev), 0) AS retailRev
    FROM combinedCTE
    GROUP BY ym, y, m
)
SELECT 
    a1.ym,
    a1.salesRev,
    ROUND((a1.salesRev / NULLIF(a2.salesRev, 0) - 1) * 100, 2) AS salesDelta, -- 加NULLIF避免除以0报错
    a1.retailRev,
    ROUND((a1.retailRev / NULLIF(a2.retailRev, 0) - 1) * 100, 2) AS retailDelta,
    a1.salesRev + a1.retailRev AS totalRev,
    ROUND(((a1.salesRev + a1.retailRev) / NULLIF(a2.salesRev + a2.retailRev, 0) - 1) * 100, 2) AS totalDelta
FROM aggregatedCTE a1
LEFT JOIN aggregatedCTE a2 ON a2.y = a1.y - 1 AND a1.m = a2.m
ORDER BY a1.ym DESC

3. 索引优化 #

给tHE_Move创建覆盖索引，让查询直接走索引无需回表，大幅提升速度：

CREATE NONCLUSTERED INDEX IX_tHE_Move_acKey_adDate_anvalue 
ON tHE_Move (acKey, adDate)
INCLUDE (anvalue);

如果acKey是固定长度的字符串，这个索引的效率会更高。

你的初始方案完全正确，属于定时批量数据刷新的常规操作，再加上上面的查询优化和存储过程小技巧，整个流程会变得既稳定又高效。

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