Início / dba / Perguntas / 202472
Accepted
DBALUKE HUANG
Asked: 2018-03-28 17:48:20 +0800 CST

Como apenas manter os registros mais recentes no servidor SQL?

  • 772

Aqui está o meu caso,

Table name: EMP_Detail
Records:
ID          Name     Statue     ChangeDate
1111        Apple    Active     2017-01-01
1111        Apple    Active     2017-03-07
1111        Apple    Disabled   2017-04-06
1111        Apple    Active     2017-05-01
2222        Bell     Active     2017-02-05
2222        Bell     Active     2018-01-05
3333        Cisco    Active     2017-03-08

Tentando remover os registros ativos e desatualizados e manter a tabela assim:

ID          Name     Statue     ChangeDate
1111        Apple    Disabled   2017-04-06
1111        Apple    Active     2017-05-01
2222        Bell     Active     2018-01-05
3333        Cisco    Active     2017-03-08

Muito obrigado!

sql-server

2 respostas

  1. Best Answer

    Você pode usar row_number()e um CTE para excluir da tabela de origem.

    ;with cte as(
   select 
        *,
       RN = row_number() over (partition by [Name], [Status] order by [ChangeDate] desc, ID desc)
   from EMP_Detail)

delete from cte
where RN != 1;
    • 4

  2. Você pode usar ROW_NUMBERpara retornar sua saída. Os pontos-chave são:

    ROW_NUMBER()OVER(PARTITION BY [Name], [Statue] ORDER BY [ChangeDate] DESC , ID DESC) AS RN

    e

    WHERE s.rn = 1

    Aqui está o código:

    CREATE TABLE EMP_Detail
    ([ID] int, [Name] varchar(5), [Statue] varchar(8), [ChangeDate] datetime)
;

INSERT INTO EMP_Detail
    ([ID], [Name], [Statue], [ChangeDate])
VALUES
    (1111, 'Apple', 'Active', '2017-01-01 00:00:00'),
    (1111, 'Apple', 'Active', '2017-03-07 00:00:00'),
    (1111, 'Apple', 'Disabled', '2017-04-06 00:00:00'),
    (1111, 'Apple', 'Active', '2017-05-01 00:00:00'),
    (2222, 'Bell', 'Active', '2017-02-05 00:00:00'),
    (2222, 'Bell', 'Active', '2018-01-05 00:00:00'),
    (3333, 'Cisco', 'Active', '2017-03-08 00:00:00')
;

SELECT
  [ID], [Name], [Statue], [ChangeDate]
FROM
(
SELECT
  [ID], [Name], [Statue], [ChangeDate]
  ,ROW_NUMBER()OVER(PARTITION BY [Name], [Statue] ORDER BY [ChangeDate] DESC , ID DESC) AS RN
FROM EMP_Detail
)S
WHERE s.rn = 1
ORDER BY [ID], [ChangeDate]

    Resultado:

    ID      Name    Statue      ChangeDate
1111    Apple   Disabled    06/04/2017
1111    Apple   Active      01/05/2017
2222    Bell    Active      05/01/2018
3333    Cisco   Active      08/03/2017

    dbfiddle aqui

    atualizar: para excluir:

    DELETE s
FROM
(
 SELECT
  [ID], [Name], [Statue], [ChangeDate]
  ,ROW_NUMBER()OVER(PARTITION BY [Name], [Statue] ORDER BY [ChangeDate] DESC , ID DESC) AS RN
FROM EMP_Detail
)S
WHERE s.rn <> 1
    • 3

relate perguntas