SQL查询-如果重复3次以上，则删除重复项？

一尘不染

SQL查询-如果重复3次以上，则删除重复项？

sql

没有人有一个优雅的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个的行。

谢谢

阅读 138

2021-03-10

共1个答案

一尘不染

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），这样LATESThas
rn=1，然后倒数第二个为rn = 2，依此类推。其余的保持不变，DELETE它将删除最旧的那些，因为它们具有最高的行号。

与这个紧密相关的问题)不同，随着条件变得越来越复杂，使用CTE和row_number（）变得更加简单。如果没有正确的访问索引，性能仍然可能会出现问题。

2021-03-10