solo - Consulta SQL: ¿Eliminar duplicados si hay más de 3 dups?

sql eliminar registros repetidos dejando solo 1

Question

¿Alguien tiene una declaración SQL elegante para eliminar registros duplicados de una tabla, pero solo si hay más de x número de duplicados? Entonces permite hasta 2 o 3 duplicados, pero eso es todo?

Actualmente tengo una declaración de selección que hace lo siguiente:

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

Esto funciona genial Pero ahora lo que me gustaría hacer es eliminar esas filas solo si tienen más de 2 duplicados.

Gracias

Answer 1

HAVING es tu amigo

select id, count(*) cnt from table group by id having cnt>2

Answer 2

Bastante tarde, pero la solución más simple podría ser la siguiente supongamos que tenemos la tabla emp_dept (empid, deptid) que tiene filas duplicadas. Aquí he usado @Count como varibale. Por ejemplo, 2 duplicados permitidos y luego @count = 2 En la base de datos Oracle

  delete from emp_dept where @Count <= ( select count(1) from emp_dept i where i.empid = emp_dept.empid and i.deptid = emp_dept.deptid and i.rowid < emp_dept.rowid ) 

En el servidor SQL o cualquier base de datos que no sea compatible con la función de identificación de fila, necesitamos agregar una columna de identidad para identificar cada fila. digamos que hemos agregado nid como identidad a la tabla

alter table emp_dept add nid int identity(1,1) -- to add identity column

Ahora la consulta para eliminar duplicados podría escribirse como

  delete from emp_dept where @@Count <= ( select count(1) from emp_dept i where i.empid = emp_dept.empid and i.deptid = emp_dept.deptid and i.nid< emp_dept.nid ) 

Aquí el concepto es eliminar todas las filas para las cuales existen otras filas que tienen valores centrales similares pero n o una mayor cantidad de rowid o identidad más pequeños. Por lo tanto, si existen filas duplicadas, se eliminará una que tenga una identificación de fila o identidad más alta. y para la fila no hay duplicado, falla al encontrar la identificación de la fila inferior, por lo tanto, no se borrará.

Answer 3

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

La consulta está generando un ''número de fila'' para cada registro, agrupado por (dupcol1, dupcol2) y ordenado por ID. En efecto, este número de fila cuenta ''duplicados'' que tienen el mismo dupcol1 y dupcol2 y asigna el número 1, 2, 3 .. N, ordena por ID. Si desea mantener solo 2 ''duplicados'', entonces debe eliminar aquellos a los que se asignaron los números 3,4,.. N y esa es la parte que cuida DELLETE.. WHERE rn > 2;

Con este método, puede cambiar el ORDER BY para que se ajuste a su orden preferida (por ejemplo, ORDER BY ID DESC ), de modo que lo LATEST rn=1 tenga rn=1 , luego el siguiente a la última es rn = 2 y así sucesivamente. El resto permanece igual, el DELETE eliminará solo los más antiguos ya que tienen los números de fila más altos.

A diferencia de esta pregunta estrechamente relacionada , a medida que la condición se vuelve más compleja, usar CTE y row_number () se vuelve más simple. El rendimiento puede ser problemático aún si no existe un índice de acceso adecuado.