有没有人有一个优雅的sql语句来删除表中的重复记录,但只有当重复数超过x时？所以它允许最多2或3个重复,但就是这样吗？
目前我有一个select语句执行以下操作：
delete table from table t left outer join ( select max(id) as rowid,dupcol1,dupcol2 from table group by dupcol1,dupcol2 ) as keeprows on t.id=keeprows.rowid where keeprows.rowid is null
这很好用.但是现在我想要做的只是删除那些行,如果他们有超过2个重复.
谢谢
解决方法
with cte as (
select row_number() over (partition by dupcol1,dupcol2 order by ID) as rn
from table)
delete from cte
where rn > 2; -- or >3 etc
该查询为每条记录制作一个“行号”,按(dupcol1,dupcol2)分组并按ID排序.实际上,此行号计数具有相同dupcol1和dupcol2的“重复”,然后按ID排序,然后分配数字1,2,3 .. N.如果你想只保留2’重复’,那么你需要删除那些分配了数字3,4,… N的那些,这是由DELLETE处理的部分.. WHERE rn> 2;
使用此方法,您可以更改ORDER BY以适合您的首选顺序(例如.ORDER BY ID DESC),以便LATEST具有rn = 1,然后最新的下一个是rn = 2,依此类推.其余部分保持不变,DELETE将仅删除最旧的那些,因为它们具有最高的行号.
与this closely related question不同,随着条件变得更加复杂,使用CTE和row_number()变得更加简单.如果不存在适当的访问索引,性能可能仍然存在问题.
