删除重复的行，但保留最早的行(按日期标准)——SQL Server

I have this table and three columns (ID, Email, [Last Update].

我有这个表和三列(ID, Email，最后更新)。

I want to delete duplicate emails but keep the last updated row (by date criteria). In this example, aaa is duplicated. It has 1 row in 2011 and other in 2014. I want to keep just 2014.

我想删除重复的电子邮件，但保留最后更新的行(按日期标准)。在本例中，aaa是重复的。2011年排名第一，2014年排名第二。我想保留2014年。

ID       Email          Last update         
a-4        aaa           10/01/2011 
b-1        bbb           10/02/2012    
k-1        ccc           05/03/2013    
d-9        aaa           10/08/2014
t-7        bbb           02/09/2015

4 个解决方案

#1

In SQL Server you can use CTE to perform the DELETE:

在SQL Server中，可以使用CTE执行删除:

;WITH ToDelete AS (
   SELECT ROW_NUMBER() OVER (PARTITION BY Email         
                             ORDER BY [Last update] DESC) AS rn
   FROM mytable
)
DELETE FROM ToDelete
WHERE rn > 1

#2

You didn't tell us which DBMS you are using, but the following is ANSI SQL and should work on all (modern) DBMS:

您没有告诉我们您使用的是哪个DBMS，但是下面是ANSI SQL，应该在所有(现代)DBMS上工作:

delete from the_table
where exists  (select id
               from the_table t2
               where t2.email = the_table.email
                 and t2.id <> the_table.id
                 and t2.last_update > the_table.last_update);

SQLFiddle: http://sqlfiddle.com/#!15/ca442/1

15 / ca442/1 SQLFiddle:http://sqlfiddle.com/ !

#3

Use the following SQL statement:

使用以下SQL语句:

SELECT First(ID) AS Id, First(Email) AS Email, Max([Last update]) AS LastUpd
FROM YourTable
GROUP BY Email
ORDER BY Max([Last update]) DESC;

and MAKE TABLE upon necessity.

并根据需要制作餐桌。

#4

To delete the duplicated records for the email field:

删除电子邮件字段的重复记录:

DELETE a FROM MyTable a INNER JOIN (
  SELECT Email, MAX([Last Update]) [Last Update]
  FROM MyTable
  GROUP BY Email
) b ON a.Email = b.Email AND a.[Last Update] <> b.[Last Update];

#1