SQL Server 2016重复T-SQL查询优化：12次日期偏移UNION改写

当然可以！你现在的写法因为重复代码太多，不仅维护起来麻烦，还容易出错。我们可以利用生成月份偏移序列的方式，把12次重复的UNION逻辑压缩成一段简洁的代码，同时保持功能完全一致。

优化后的代码 #

这里有两种常用的实现方式，都适配SQL Server 2016：

方式一：直接定义固定偏移序列（最直观）

DECLARE @date DATE = '2024-06-01'; -- 替换成你的实际变量

WITH MonthOffsets AS (
    -- 生成0到11的偏移量，对应12个月份的查询逻辑
    SELECT n FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11)) AS nums(n)
)
SELECT z.*
FROM MonthOffsets mo
CROSS APPLY (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [action_timestamp] DESC) AS rowNum,
        [new_state] AS [Last_State],
        DATEADD(MONTH, -mo.n, @date) AS [date]
    FROM [dbo].[CR_hist_Data]
    WHERE [action_timestamp_formatted] < DATEADD(MONTH, -(mo.n + 1), @date)
) z
WHERE z.rowNum = 1;

方式二：递归CTE生成序列（适合动态调整月份数）

如果以后需要增加或减少查询的月份数量，递归CTE的方式会更灵活：

DECLARE @date DATE = '2024-06-01';
DECLARE @monthCount INT = 12; -- 可以直接修改这个值调整月份数

WITH MonthOffsets AS (
    SELECT 0 AS n
    UNION ALL
    SELECT n + 1 FROM MonthOffsets WHERE n < @monthCount - 1
)
SELECT z.*
FROM MonthOffsets mo
CROSS APPLY (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [action_timestamp] DESC) AS rowNum,
        [new_state] AS [Last_State],
        DATEADD(MONTH, -mo.n, @date) AS [date]
    FROM [dbo].[CR_hist_Data]
    WHERE [action_timestamp_formatted] < DATEADD(MONTH, -(mo.n + 1), @date)
) z
WHERE z.rowNum = 1
OPTION (MAXRECURSION 0); -- 如果月份数超过100，必须添加这个选项

优化方案的优势 #

• 简洁易维护：核心查询逻辑只写一次，不用重复复制12遍，后续修改条件或月份数时只需调整一处
• 性能更稳定：SQL Server的查询优化器能更好地处理这种结构化查询，避免重复生成相同的执行计划
• 扩展性强：如果需要扩展到18个月或缩短到6个月，只需修改序列生成部分的参数即可

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