主页 / dba / 问题 / 239266
Accepted
learning
Asked: 2019-05-29 16:54:42 +0800 CST

根据最新的时间戳条目删除重复项

  • 772
I have a table and I'm trying to remove all the duplicate and keep the  
the rows that has the latest datestamp.

Here is the table:

email address       orgin_date   new_opt_in_date   datestamp
[email protected]            1900-1-1     1900-1-1          2016-3-15
[email protected]            1900-1-1     1900-1-1          2016-3-15
[email protected]  2015-2-2     2016-12-26        2017-1-19
[email protected]  2015-2-2     2016-12-26        2018-6-6
[email protected]        2016-3-15    2016-3-151        2019-1-23
[email protected]        2016-3-15   2016-3-151         2018-5-6

I'm trying to keep only the data that has the recent datestamp, delete the 
rest and hope that the
output will like this:
email address       orgin_date   new_opt_in_date   datestamp
 [email protected]            1900-1-1     1900-1-1          2016-3-15
 [email protected]  2015-2-2     2016-12-26        2018-6-6
 [email protected]        2016-3-15    2016-3-151        2019-1-23

DELETE FROM `tablename` 
WHERE datestamp
NOT IN (
SELECT * FROM (
  SELECT MAX(datestamp) FROM tablename 
    GROUP BY emailaddress
 ) 
 )    
but nothing it didn't work
sql-server t-sql

1 个回答

  1. Best Answer

    您可以使用 CTE 为每个电子邮件地址添加从最新日期戳开始的 1 的行号，然后删除所有其余的。

    ;WITH x AS 
(
  SELECT email, origin_date, new_opt_in_date, datestamp,
    rn = ROW_NUMBER() OVER (PARTITION BY email ORDER BY datestamp DESC)
  FROM dbo.tablename
)
DELETE x WHERE rn > 1;
    • 4

相关问题