SQL Server存储过程，用于在添加新记录时转储最旧的X记录

Question

我有一个许可场景，当一个人激活一个新系统时，它会将旧的激活添加到一个锁定表中，因此他们只能激活他们最新的X系统。我需要传递一个参数，指定要保留多少最近的激活，以及所有旧的激活应该添加到锁定表中(如果它们尚未锁定)。我不知道怎么做才是最好的，比如临时表(我从来没有做过)等等。

例如，激活来自系统XYZ上的John Doe。然后，我需要查询由John Doe进行的所有激活的激活表，并按日期DESC对其进行排序。在这种情况下，John Doe可能有一个允许两个系统的许可证，所以我需要停用前两个系统之前的所有记录，即插入到锁定表中。

提前感谢您的帮助。

Answer 1

也许是这样的东西？

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

Answer 2

通过视图或表值函数耦合到row_number()可能是最简单的方法：

WITH ActivationRank AS
(
SELECT SystemId,ProductId,CreatedDate,ROW_NUMBER() OVER(PARTITION BY ProductId ORDER BY CreatedDate DESC) AS RANK     
FROM [Activations]
)

SELECT SystemId, ProductId, CASE WHEN RANK < @lockoutParameterOrConstant 0 ELSE 1 END AS LockedOut
FROM ActivationRank

Answer 3

在你花时间阅读和尝试我的方法之前，我想说Joe Stefanelli的答案是一个很好的答案-简短，紧凑，先进，可能比我的更好，特别是在性能方面。另一方面，性能可能不是您首先关心的问题(您预计每天会有多少激活？每小时？每分钟？)而且我的例子可能更容易阅读和理解。

由于我不知道您的数据库模式是如何设置的，因此我不得不对其进行一些假设。您可能无法将此代码用作复制和粘贴模板，但它应该能让您了解如何做到这一点。

您正在谈论一个锁定表，所以我认为您有理由将部分数据复制到第二个表中。如果可能，我更愿意在包含系统数据的表中使用锁定标志，但显然这取决于您的方案。

请注意，我目前无法访问SQL Server，因此无法检查代码的有效性。我尽了最大的努力，但也可能会有打字错误。

第一个假设:一个最低限度的“注册系统”表：

CREATE TABLE registered_systems 
   (id INT NOT NULL IDENTITY, 
    owner_id INT NOT NULL,
    system_id VARCHAR(MAX) NOT NULL, 
    activation_date DATETIME NOT NULL)

第二个假设:一个极简主义的“锁定系统”表：

CREATE TABLE locked_out_systems 
   (id INT NOT NULL, 
    lockout_date DATETIME NOT NULL)

然后我们可以定义一个存储过程来激活一个新系统。它接受owner_id、允许的系统数量，当然还有新的系统id作为参数。

CREATE PROCEDURE register_new_system 
   @owner_id INT, 
   @allowed_systems_count INT, 
   @new_system_id VARCHAR(MAX)
AS
BEGIN TRANSACTION
   -- Variable declaration
   DECLARE @sid INT  -- Storage for a system id

   -- Insert the new system
   INSERT INTO registered_systems 
      (owner_id, system_id, activation_date)
   VALUES
      (@owner_id, @system_od, GETDATE())

   -- Use a cursor to query all registered-and-not-locked-out systems for this
   -- owner. Skip the first @allowed_systems_count systems, then insert the
   -- remaining ones into the lockout table.
   DECLARE c_systems CURSOR FAST_FORWARD FOR
      SELECT system_id FROM
         registered_systems r
      LEFT OUTER JOIN 
         locked_out_systems l
      ON r.system_id = l.system_id
      WHERE l.system_id IS NULL
      ORDER BY r.activation_date DESC

   OPEN c_systems

   FETCH NEXT FROM c_systems INTO @sid

   WHILE @@FETCH_STATUS = 0
   BEGIN       
       IF @allowed_systems_count > 0
          -- System still allowed, just decrement the counter
          SET @allowed_systems_count = @allowed_systems_count -1
       ELSE
          -- All allowed systems used up, insert this one into lockout table
          INSERT INTO locked_out_systems 
             (id, lockout_date)
          VALUES
             (@sid, GETDATE())

       FETCH NEXT FROM c_systems INTO @sid
   END

   CLOSE c_systems
   DEALLOCATE c_systems

COMMIT