SQL Server 存储过程在添加新记录时转储最旧的 X 记录 [英] SQL Server Stored Procedure to dump oldest X records when new records added

问题描述

我有一个许可方案，当一个人激活一个新系统时，它会将旧的激活添加到锁定表中，因此他们只能激活他们最新的 X 系统.我需要传递一个参数，说明要保留多少最近的激活，如果尚未锁定，则应将所有较旧的激活添加到锁定表中.我不确定如何最好地做到这一点，即临时表(我从未做过)等.

I have a licensing scenario where when a person activates a new system it adds the old activations to a lockout table so they can only have their latest X systems activated. I need to pass a parameter of how many recent activations to keep and all older activations should be added to the lockout table if they are not already locked out. I'm not sure how best to do this, i.e. a temp table (which I've never done) etc.

例如，激活来自系统 XYZ 上的 John Doe.然后，我需要查询 John Doe 的所有激活的激活表，并按 DATE DESC 对其进行排序.在这种情况下，John Doe 可能拥有允许两个系统的许可证，因此我需要停用比前两个系统更旧的所有记录，即插入到锁定表中.

For example, an activation comes in from John Doe on System XYZ. I would then need to query the activations table for all activations by John Doe and sort it by DATE DESC. John Doe may have a license allowing two systems in this case so I need all records older than the top 2 deactivated, i.e. inserted into a lockouts table.

预先感谢您的帮助.

推荐答案

可能是这样的?

insert into lockouts
    (<column list>)
    select <column list>
        from (select <column list>, 
                     row_number() over (order by date desc) as RowNum
                  from activations) t
        where t.RowNum > @NumLicenses

这篇关于SQL Server 存储过程在添加新记录时转储最旧的 X 记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！