主页 / dba / 问题 / 202472
Accepted
DBALUKE HUANG
Asked: 2018-03-28 17:48:20 +0800 CST

如何只在 SQL Server 中保留最新记录？

  • 772

这是我的情况，

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

尝试删除活动和过时的记录并保留如下表格：

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

非常感谢！

sql-server

2 个回答

  1. Best Answer

    您可以使用row_number()和一个 CTE 从源表中删除。

    ;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. 您可以使用它ROW_NUMBER来返回您的输出。关键点是：

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

    和

    WHERE s.rn = 1

    这是代码：

    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]

    输出：

    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在这里

    更新：删除：

    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

相关问题