MySQL-删除重复的记录，但保留XX条记录

Question

我的问题是我想删除重复的记录，但保留了XX条最新的记录。例如：

价格>国家有效期美国美国2022-03-28美国2022-03-26美国2022-03-302022-03-28美国2022-03-26美国2022-04-01

id	ean	价格类型
1	12345678	19.99	b2c	2022-03-30
2	1234678	18.99	b2c
3	1234678	17.99	b2c
4	11122233	146.99	b2b
5	11122233	150.99	b2b	美国
6	11122233	170.99	b2b
7	11122233	180.99	b2b

MySQL的精确版本是什么Akina

MySQL 5.7–nphuly

DELETE t0
FROM test t0
JOIN ( SELECT t1.id, COUNT(*) cnt
FROM test t1
JOIN test t2 ON t1.ean = t2.ean AND t1.valid_to <= t2.valid_to
GROUP BY t1.id ) t3 ON t0.id = t3.id
WHERE t3.cnt > 2

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=3284d0a1272813c1fbd6121bfd51b3

支持MSSQL 8.0或更高版本的

将ROW_NUMBER与PARTITION一起使用并获得结果

每个ean 只获取2条记录

SELECT * FROM ( 
SELECT id,ean,price,price_type,country,valid_to,
ROW_NUMBER() OVER(PARTITION BY valid_to ORDER BY ean,valid_to) AS RNO 
FROM tablename
)main 
WHERE RNO < 3

如果你想删除，那么使用这个

DELETE FROM tablename WHERE id IN (
SELECT Id FROM (
SELECT id,
ROW_NUMBER() OVER(PARTITION BY valid_to ORDER BY ean,valid_to) AS RNO 
FROM tablename
)main
WHERE RNO > 2
)