假设我有一个包含列的表:
CustomerNumber
Lastname
Firstname
PurchaseDate
...以及其他在此处未显示不会改变问题的列。
在此表中,我可以为同一客户拥有许多具有不同购买日期的行(我知道,设计很差...我只想解决报告问题,而不是真的试图解决问题的根源)。
如何在SQL中保留每个客户的一条记录,并删除其余记录的最新日期?对于我的情况,group by
似乎不起作用。
假设我有一个包含列的表:
CustomerNumber
Lastname
Firstname
PurchaseDate
...以及其他在此处未显示不会改变问题的列。
在此表中,我可以为同一客户拥有许多具有不同购买日期的行(我知道,设计很差...我只想解决报告问题,而不是真的试图解决问题的根源)。
如何在SQL中保留每个客户的一条记录,并删除其余记录的最新日期?对于我的情况,group by
似乎不起作用。
;with a as
(
select row_number() over (partition by CustomerNumber, Lastname, Firstname order by PurchaseDate desc) rn
from <table>
)
delete from a where rn > 1
这对我有用(在DB2上):
DELETE FROM my_table
WHERE (CustomerNumber, Lastname, Firstname, PurchaseDate)
NOT IN (
SELECT CustomerNumber, Lastname, Firstname, MAX(PurchaseDate)
FROM my_table
GROUP BY CustomerNumber, Lastname, FirstName
)
SELECT CustomerNumber, Lastname, Firstname, MAX(PurchaseDate) LatestPurchaseDate
FROM Table
GROUP BY CustomerNumber, Lastname, Firstname
MAX函数将选择最高(最新)日期,并为每个GROUP BY列的唯一组合显示该日期。
编辑:我误解了您想要删除除最新购买日期之外的所有记录。
WITH Keep AS
(
SELECT CustomerNumber, Lastname, Firstname, MAX(PurchaseDate) LatestPurchaseDate
FROM Table
GROUP BY CustomerNumber, Lastname, Firstname
)
DELETE FROM Table
WHERE NOT EXISTS
(
SELECT *
FROM Keep
WHERE Table.CustomerNumber = Keep.CustomerNumber
AND Table.Lastname = Keep.Lastname
AND Table.Firstname = Keep.Firstname
AND Table.PurchaseDate = Keep.LastPurchaseDate
)