Блог

Главная - Вопросы по программированию - Как мне удалить строки возрастом более 6 месяцев, но при этом убедиться, что в таблице с использованием SQL осталось не менее 100 строк?

Как мне удалить строки возрастом более 6 месяцев, но при этом убедиться, что в таблице с использованием SQL осталось не менее 100 строк?

#sql #sql-server-2016

#sql #sql-server-2016

Вопрос:

Рассмотрим следующее определение таблицы:

 FooTable
   ID int,
   TimeStamp datetime,
   Data varchar(50)

Я хотел бы сохранить только данные за последние 6 месяцев, но также убедиться, что в таблице хранится не менее 100 строк данных. Как мне это сделать?

Я знаю, как удалить данные возрастом более 6 месяцев:

 DELETE
FROM FooTable
WHERE TimeStamp <=  DATEADD(MONTH, -6, GETDATE())

Как мне добавить условие сохранения не менее 100 строк данных в таблице (независимо от того, как хранятся данные)?

Спасибо, JohnB

Комментарии:

1. Пожалуйста, пометьте используемую вами СУБД, например, SQL Server 2019

Ответ №1:

Я думаю, вы хотите:

 WITH todelete AS (
      SELECT f.*,
             ROW_NUMBER() OVER (ORDER BY TimeStamp DESC) as seqnum
      FROM FooTable 
     )
DELETE FROM todelete
WHERE seqnum > 100 AND
      TimeStamp < DATEADD(MONTH, -6, GETDATE());

При этом удаляются самые старые временные метки, но при этом они удаляются только после 100-й записи.

Ответ №2:

Я полагаю, вы будете использовать этот код в SSMS для очистки данных, поэтому вам будет все равно, включает ли он некоторые переменные.

Итак, мое решение таково:

 DECLARE @CURRENT_NO INT
SELECT @CURRENT_NO = COUNT(*) FROM xTable WHERE timstamp < DATEADD(MONTH, -6, GETDATE())
DECLARE @DEL_NO INT
SELECT @DEL_NO = CASE WHEN @CURRENT_NO < 100 THEN 100 ELSE @CURRENT_NO END 
DECLARE @DEL_ROW_NO INT
SELECT @DEL_ROW_NO = COUNT(*)   @DEL_NO - 100 FROM xTable WHERE timstamp > DATEADD(MONTH, -6, GETDATE())
DELETE TOP (@DEL_ROW_NO) FROM xTable WHERE timstamp < DATEADD(MONTH, -6, GETDATE())
SELECT * FROM xTable

Я протестировал его с помощью приведенного ниже сценария (уменьшенная версия):

 CREATE TABLE xTable (timstamp DATETIME, qty INT)
INSERT INTO xTable VALUES
('2020-11-01', 5), ('2020-01-01', 5), ('2020-01-01', 5), ('2020-01-01', 5), ('2020-01-01', 5), 
('2020-11-01', 5), ('2020-01-01', 5), ('2020-01-01', 5), ('2020-01-01', 5), ('2020-01-01', 5)

DECLARE @CURRENT_NO INT
SELECT @CURRENT_NO = COUNT(*) FROM xTable WHERE timstamp < DATEADD(MONTH, -6, GETDATE())
DECLARE @DEL_NO INT
SELECT @DEL_NO = CASE WHEN @CURRENT_NO < 5 THEN 5 ELSE @CURRENT_NO END 
SELECT @DEL_NO
DECLARE @DEL_ROW_NO INT
SELECT @DEL_ROW_NO = COUNT(*)   @DEL_NO - 5 FROM xTable WHERE timstamp > DATEADD(MONTH, -6, GETDATE())
SELECT @DEL_ROW_NO
DELETE TOP (@DEL_ROW_NO) FROM xTable WHERE timstamp < DATEADD(MONTH, -6, GETDATE())
SELECT * FROM xTable

DROP TABLE xTable

и в результате получается :

  ------------------------- ----- 
| timstamp                | qty |
 ------------------------- ----- 
| 2020-11-01 00:00:00.000 | 5   |
| 2020-11-01 00:00:00.000 | 5   |
| 2020-01-01 00:00:00.000 | 5   |
| 2020-01-01 00:00:00.000 | 5   |
| 2020-01-01 00:00:00.000 | 5   |
 ------------------------- -----
Метки: , ,