SQL Server中基于表名循环迭代实现多月份数据统计方案

针对SQL Server中逐月新增的历史表+当月表的统计需求，推荐以下两种自动处理方案，避免手动编写重复关联逻辑：

方案一：动态SQL自动生成关联查询 #

通过查询系统表获取所有历史月份表，动态拼接SQL实现全量关联（用FULL OUTER JOIN避免丢失仅存在于部分表的flag值）：

DECLARE @DynamicSQL NVARCHAR(MAX)
DECLARE @JoinClauses NVARCHAR(MAX) = ''
DECLARE @FlagCoalesce NVARCHAR(MAX) = ''
DECLARE @CountColumns NVARCHAR(MAX) = ''

-- 收集所有历史表的关键信息，生成关联子句、flag合并字段、统计列字段
SELECT 
    @JoinClauses += '
FULL OUTER JOIN (
    SELECT COUNT(*) AS count_' + REPLACE(REPLACE(name, 'tab_', ''), '_', '') + ', flag_col
    FROM ' + QUOTENAME(name) + '
    GROUP BY flag_col
) t' + REPLACE(REPLACE(name, 'tab_', ''), '_', '') + ' 
    ON COALESCE(main.flag_col, ' + @FlagCoalesce + ') = t' + REPLACE(REPLACE(name, 'tab_', ''), '_', '') + '.flag_col',
    @FlagCoalesce += IIF(@FlagCoalesce = '', '', ', ') + 't' + REPLACE(REPLACE(name, 'tab_', ''), '_', '') + '.flag_col',
    @CountColumns += IIF(@CountColumns = '', '', ', ') + 't' + REPLACE(REPLACE(name, 'tab_', ''), '_', '') + '.count_' + REPLACE(REPLACE(name, 'tab_', ''), '_', '')
FROM sys.tables
WHERE name LIKE 'tab_[0-9][0-9]_[0-9][0-9][0-9][0-9]' -- 匹配tab_MM_YYYY格式的历史表

-- 拼接完整SQL，以当月表作为基础
SET @DynamicSQL = '
SELECT 
    COALESCE(main.flag_col, ' + @FlagCoalesce + ') AS flag_col,
    main.count_current,
    ' + @CountColumns + '
FROM (
    SELECT COUNT(*) AS count_current, flag_col
    FROM tab_master
    GROUP BY flag_col
) main' + @JoinClauses

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL

说明： #

• 自动识别所有tab_MM_YYYY格式的历史表，新增表后无需手动修改SQL
• 用COALESCE处理不同表中flag值的匹配，避免丢失仅存在于部分表的flag数据
• 每个历史月份的统计列命名为count_YYYYMM（比如count_202208）

方案二：UNION ALL合并数据后转置（PIVOT） #

先将所有表的数据合并并带上月份标识，再通过PIVOT转置为按月份列展示的统计结果：

DECLARE @DynamicSQL NVARCHAR(MAX)
DECLARE @MonthColumns NVARCHAR(MAX) = ''
DECLARE @UnionAllParts NVARCHAR(MAX) = ''

-- 生成所有表的UNION ALL部分（含当月表）
SELECT 
    @UnionAllParts += '
SELECT flag_col, ''' + IIF(name = 'tab_master', 'current', REPLACE(REPLACE(name, 'tab_', ''), '_', '')) + ''' AS month_key
FROM ' + QUOTENAME(name),
    @MonthColumns += IIF(@MonthColumns = '', '', ', ') + '[' + IIF(name = 'tab_master', 'current', REPLACE(REPLACE(name, 'tab_', ''), '_', '')) + ']'
FROM sys.tables
WHERE name = 'tab_master' OR name LIKE 'tab_[0-9][0-9]_[0-9][0-9][0-9][0-9]'

-- 拼接完整PIVOT SQL
SET @DynamicSQL = '
SELECT flag_col, ' + @MonthColumns + '
FROM (
    ' + STUFF(@UnionAllParts, 1, 2, '') + ' -- 移除开头的换行和空格
) AS src
PIVOT (
    COUNT(month_key)
    FOR month_key IN (' + @MonthColumns + ')
) AS pvt'

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL

说明： #

• 逻辑更直观，先合并所有数据再统计转置
• 自动包含当月表和所有历史表，新增表后无需修改代码
• 统计列直接显示月份标识（current代表当月，202208代表对应历史月份）

最佳实践：避免分表，改用单表+分区 #

长期来看，逐月分表会增加维护复杂度，建议优化数据存储结构：

• 将所有数据合并到一张表（比如tab_all_data），新增month_column字段存储数据所属月份（格式为yyyyMM）
• 若数据量较大，可按month_column创建分区表，兼顾查询性能和维护便捷性
• 此时统计查询只需简单的分组和条件过滤，若需自动识别所有月份，同样可以用动态SQL生成SUM(CASE...)子句：

SELECT 
    flag_col,
    SUM(CASE WHEN month_column = FORMAT(GETDATE(), 'yyyyMM') THEN 1 ELSE 0 END) AS count_current,
    SUM(CASE WHEN month_column = '202208' THEN 1 ELSE 0 END) AS count_202208,
    SUM(CASE WHEN month_column = '202209' THEN 1 ELSE 0 END) AS count_202209
FROM tab_all_data
GROUP BY flag_col

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