SQL 查询 - 以更简单的方式重写

Question

使用 SQL Server 2016

我有以下 T-SQL 查询，可以满足我的需要，但它包含大量重复。有没有办法以更优雅的方式重写它？

SELECT z.*
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [action_timestamp] DESC) AS rowNum,
           [new_state] AS [Last_State],
           DATEADD(MONTH, 0, @date) AS [date]
    FROM [dbo].[CR_hist_Data]
    WHERE [action_timestamp_formatted] < DATEADD(MONTH, -1, @date)
) z
WHERE z.rowNum = 1
UNION
SELECT z.*
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [action_timestamp] DESC) AS rowNum,
           [new_state] AS [Last_State],
           DATEADD(MONTH, -1, @date) AS [date]
    FROM [dbo].[CR_hist_Data]
    WHERE [action_timestamp_formatted] < DATEADD(MONTH, -2, @date)
) z
WHERE z.rowNum = 1
UNION
SELECT z.*
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [id] ORDER BY [action_timestamp] DESC) AS rowNum,
           [new_state] AS [Last_State],
           DATEADD(MONTH, -2, @date) AS [date]
    FROM [dbo].[CR_hist_Data]
    WHERE [action_timestamp_formatted] < DATEADD(MONTH, -3, @date)
) z
WHERE z.rowNum = 1;

etc....for 12 times

正如您所看到的，我对同一语句进行了 12 次 UNION，日期偏移为一个月。请指教。问候。

Answer 1

测试脚本很困难，但是您可以尝试以下方法:

SELECT z.*
FROM
(
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY [id], DATEADD(MONTH,-1 * t.num, @date) ORDER BY [action_timestamp] DESC) AS rowNum,
           [new_state] AS [Last_State],
           DATEADD(MONTH, t.num, @date) AS [date]
    FROM [dbo].[CR_hist_Data]
    CROSS JOIN (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)  )  t(num)
    WHERE [action_timestamp_formatted] < DATEADD(MONTH, -1 * t.num, @date)
) z
WHERE z.rowNum = 1