SQL Server实现每月最后3个唯一loaddate查询及400+表批量处理

一、单表自动遍历所有有数据月份的逻辑 #

通过窗口函数按月份分区排序，自动提取每个月的最后3个唯一loaddate，无需手动指定月份：

WITH MonthDateCTE AS (
    SELECT DISTINCT
        loaddate,
        DATEFROMPARTS(YEAR(loaddate), MONTH(loaddate), 1) AS month_start,
        ROW_NUMBER() OVER (
            PARTITION BY DATEFROMPARTS(YEAR(loaddate), MONTH(loaddate), 1) 
            ORDER BY loaddate DESC
        ) AS rn
    FROM YourTableName
    WHERE loaddate IS NOT NULL
)
SELECT 
    month_start AS [month],
    loaddate
FROM MonthDateCTE
WHERE rn <= 3
ORDER BY month_start DESC, loaddate DESC;

逻辑说明 #

• 用DATEFROMPARTS将每个loaddate映射到所在月份的第一天，作为分组依据
• ROW_NUMBER()按月份分区，以loaddate倒序排序，标记每条记录的排名
• DISTINCT确保loaddate唯一，避免重复日期被多次统计
• 筛选排名≤3的记录，得到每个月最后3个日期

二、批量处理400+张表的动态SQL方案 #

通过查询系统元数据自动识别所有包含loaddate列的表，批量生成并执行查询：

DECLARE @SQL NVARCHAR(MAX) = '';

-- 生成所有目标表的查询语句
SELECT @SQL += '
WITH MonthDateCTE AS (
    SELECT DISTINCT
        loaddate,
        DATEFROMPARTS(YEAR(loaddate), MONTH(loaddate), 1) AS month_start,
        ROW_NUMBER() OVER (
            PARTITION BY DATEFROMPARTS(YEAR(loaddate), MONTH(loaddate), 1) 
            ORDER BY loaddate DESC
        ) AS rn
    FROM ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '
    WHERE loaddate IS NOT NULL
)
SELECT 
    ''' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ''' AS table_name,
    month_start AS [month],
    loaddate
FROM MonthDateCTE
WHERE rn <= 3
ORDER BY month_start DESC, loaddate DESC;
'
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
WHERE c.name = 'loaddate'; -- 筛选包含loaddate列的表

-- 执行动态SQL
EXEC sp_executesql @SQL;

关键细节 #

• 使用sys.tables、sys.schemas、sys.columns系统视图定位所有目标表
• QUOTENAME函数处理带特殊字符的表名/架构名，避免语法错误
• 每个查询结果增加table_name字段，区分不同表的输出数据
• 若需限定日期范围（如示例的2021年2月至2022年12月），可在WHERE loaddate IS NOT NULL后追加AND loaddate BETWEEN ''2021-02-01'' AND ''2022-12-31''

三、注意事项 #

• 若部分表的loaddate为字符串类型，需先转换为日期类型，例如CAST(loaddate AS DATE)，否则DATEFROMPARTS会报错
• 执行动态SQL需具备足够权限（如VIEW DEFINITION、表查询权限）
• 可将结果插入统一报表表，方便后续分析，只需在每个SELECT前添加INSERT INTO ReportTable(table_name, [month], loaddate)

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