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

Question

Using 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次。 请指教。 问候。

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

Answer 2

用例：

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

Answer 3

我认为使用通用表表达式可能会有所帮助。

with dates(r, currmonth, lastmonth) as
(select 1 r, eomonth(@date) CurrMonth, eomonth(@date,-1) LastMonth
union all
select r+1, eomonth(@date,-1*r), eomonth(@date,-1*(r+1)) from dates
where r < 12)
SELECT z.*
FROM
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY [id],dates.r ORDER BY [action_timestamp] DESC) AS rowNum,
[new_state] AS [Last_State],
dates.currmonth AS [date]
FROM [dbo].[CR_hist_Data]
join dates on 1=1
WHERE [action_timestamp_formatted] < dates.lastmonth
) z
WHERE z.rowNum = 1