SQL Server：存储最近5个日期

Looking for advice on how I go about writing this code.

寻找关于如何编写此代码的建议。

I have a table of unique values being ported over from one system to another. And I want to store a record of say the last 5 dates of when the record was brought over.

我有一个从一个系统移植到另一个系统的唯一值表。而且我想记录一下记录结束时的最后5个日期。

So for example, Item A is refreshed from System X to System Y on the 1st October 2017, I want to store this date in the table. Then over the next month, it is brought over another 4 times, so I want these date values also written into that table.

例如，项目A在2017年10月1日从System X刷新到System Y，我想将此日期存储在表格中。然后在接下来的一个月里，它被带来了4次，所以我希望这些日期值也写入该表。

Now that the last 5 dates have been written, I need some way of maintaining the code so that any new dates that come in will overwrite the table data, so that we will always have at most the last 5 update dates in this table.

既然已经写了最后5个日期，我需要一些方法来维护代码，以便任何新的日期都会覆盖表数据，这样我们在这个表中最多只能有最后5个更新日期。

Example Data; The date columns represent the last 5 updates that have occured for each property row.

示例数据;日期列表示每个属性行发生的最后5次更新。

Property ID's, Date 1,   Date 2,   Date 3,  Date 4,   Date 5
1              01/07/17, 01/08/17 01/10/17 05/10/17 10/10/17
2              01/01/17 01/03/17 01/06/17 05/10/17 10/10/17
3              01/02/17 05/02/17 01/10/17 05/10/17 10/10/17
4              01/03/17 01/08/17 01/10/17

If property 4 has an update from system X, date 4 gets populated. If property 3 has an update from system x, Dates 2 to 5 would move left one position and date 5 would get populated with the latest date.

如果属性4具有来自系统X的更新，则填充日期4。如果属性3具有来自系统x的更新，则日期2到5将向左移动一个位置，日期5将填充最新日期。

Would this be the best way of recording last 5 updates.

这是记录最近5次更新的最佳方式吗？

Alternatively, i could just write each property id and date of update into a table and have some sort of cleaning routine of only keeping 5 entries per property.

或者，我可以将每个属性id和更新日期写入表中，并进行某种清理例程，每个属性只保留5个条目。

Apologies if I'm being vague.

如果我含糊不清，请道歉。

2 个解决方案

#1

Better use table like:

更好地使用表格：

CREATE TABLE dbo.ChangeDates (
    PropertyID int,
    DateChanged date
)

And just write data in it. Then create view like:

只需在其中写入数据。然后创建视图，如：

CREATE VIEW dbo.Top5ChangeDates 
AS
WITH cte AS (
    SELECT  PropertyID,
            DateChanged,
            ROW_NUMBER() OVER (PARTITION BY PropertyID ORDER BY DateChanged DESC) as rn
    FROM dbo.ChangeDates
)

SELECT  PropertyID,
        DateChanged
FROM cte
WHERE rn <= 5
GO

Or use after insert TRIGGER instead of view, to clear data.

或者在插入TRIGGER而不是查看后使用，以清除数据。

#2

If you want to keep the most recent 5 records then it seems like MERGE, allowing simultaneous INSERT and DELETE activity may be the way to go.

如果你想保留最近的5条记录，那么它似乎是MERGE，允许同时进行INSERT和DELETE活动。

This script demonstrates a MERGE that keeps the most recent 5 rows per ID value:

此脚本演示了一个MERGE，它保留每个ID值最近的5行：

create table #History (ID int not null, OccurredAt datetime2(7) not null)
go
declare @current table (ID int not null)
insert into @current (ID) values (1),(3)

;With Keeps as (
    select
        *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY OccurredAt desc) as rn
    from #History
    where ID in (select ID from @current)
)
merge into #History t
using (select ID,SYSDATETIME() from @current
        union all
      select ID,OccurredAt from Keeps where rn between 1 and 4) s(ID,OccurredAt)
on
    t.ID = s.ID and
    t.OccurredAt = s.OccurredAt
when not matched then insert (ID,OccurredAt) values (s.ID,s.OccurredAt)
when not matched by source and t.ID in (select ID from @current) then delete;

waitfor delay '00:00:01'
go 30
select * from #History

Hopefully you can see how the CTE finds the "other" rows that should be preserved and how the two not matched clauses then handle the INSERT and DELETE activities.

希望您可以看到CTE如何找到应保留的“其他”行以及两个不匹配的子句如何处理INSERT和DELETE活动。

#1