代码之家 › 专栏 › 技术社区 › Imran Saleem

SQL Server:存储最近5个日期

pseudocode sql-server

Imran Saleem · 技术社区 · 7 年前

我有一个从一个系统移植到另一个系统的独特值表。我想存储一个记录,比如说记录被带过来的最后5个日期。

例如,项目A在2017年10月1日从系统X刷新到系统Y,我想将该日期存储在表中。然后在接下来的一个月里,它又被带了4次,所以我想把这些日期值也写进那个表中。

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

示例数据; 日期列表示每个属性行最近发生的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

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

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

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

如果我含糊其辞,请道歉。

2 回复 | 直到 7 年前

gofr1 7 年前

更好地使用表格:

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

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

或者使用插入后触发器而不是视图来清除数据。

Damien_The_Unbeliever 7 年前

如果你想保留最近的5条记录,那么 MERGE ,允许同时 INSERT 和 DELETE 活动可能是一条路。

此脚本演示了一个 合并 这将保留每个 ID 价值:

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

希望您能看到CTE如何找到应该保留的“其他”行,以及这两个行是如何被保留的 not matched 然后子句处理 插入 活动。