Es fácil encontrar duplicados con un solo campo:
SELECT name, COUNT(email)
FROM users
GROUP BY email
HAVING COUNT(email) > 1
Así que si tenemos una tabla
idioma: lang-none -->
ID NAME EMAIL
1 John [email protected]
2 Sam [email protected]
3 Tom [email protected]
4 Bob [email protected]
5 Tom [email protected]
Esta consulta nos dará John, Sam, Tom, Tom porque todos tienen el mismo email
.
Sin embargo, lo que quiero es obtener duplicados con el mismo "correo electrónico" y "nombre".
Es decir, quiero obtener "Tom", "Tom".
La razón por la que necesito esto: Cometí un error y permití insertar valores duplicados de nombre
y email
. Ahora necesito eliminar/cambiar los duplicados, así que necesito encontrarlos primero.
SELECT
name, email, COUNT(*)
FROM
users
GROUP BY
name, email
HAVING
COUNT(*) > 1
Simplemente agrupa las dos columnas.
Nota: el estándar ANSI más antiguo es tener todas las columnas no agregadas en el GROUP BY pero esto ha cambiado con la idea de "dependencia funcional":
En la teoría de las bases de datos relacionales, una dependencia funcional es una restricción entre dos conjuntos de atributos en una relación de una base de datos. En otras palabras, la dependencia funcional es una restricción que describe la relación entre los atributos de una relación.
El soporte no es consistente:
sql_mode=only_full_group_by
:prueba esto:
declare @YourTable table (id int, name varchar(10), email varchar(50))
INSERT @YourTable VALUES (1,'John','John-email')
INSERT @YourTable VALUES (2,'John','John-email')
INSERT @YourTable VALUES (3,'fred','John-email')
INSERT @YourTable VALUES (4,'fred','fred-email')
INSERT @YourTable VALUES (5,'sam','sam-email')
INSERT @YourTable VALUES (6,'sam','sam-email')
SELECT
name,email, COUNT(*) AS CountOf
FROM @YourTable
GROUP BY name,email
HAVING COUNT(*)>1
SALIDA:
name email CountOf
---------- ----------- -----------
John John-email 2
sam sam-email 2
(2 row(s) affected)
si quieres los IDs de los dups usa esto:
SELECT
y.id,y.name,y.email
FROM @YourTable y
INNER JOIN (SELECT
name,email, COUNT(*) AS CountOf
FROM @YourTable
GROUP BY name,email
HAVING COUNT(*)>1
) dt ON y.name=dt.name AND y.email=dt.email
SALIDA:
id name email
----------- ---------- ------------
1 John John-email
2 John John-email
5 sam sam-email
6 sam sam-email
(4 row(s) affected)
para eliminar los duplicados prueba:
DELETE d
FROM @YourTable d
INNER JOIN (SELECT
y.id,y.name,y.email,ROW_NUMBER() OVER(PARTITION BY y.name,y.email ORDER BY y.name,y.email,y.id) AS RowRank
FROM @YourTable y
INNER JOIN (SELECT
name,email, COUNT(*) AS CountOf
FROM @YourTable
GROUP BY name,email
HAVING COUNT(*)>1
) dt ON y.name=dt.name AND y.email=dt.email
) dt2 ON d.id=dt2.id
WHERE dt2.RowRank!=1
SELECT * FROM @YourTable
SALIDA:
id name email
----------- ---------- --------------
1 John John-email
3 fred John-email
4 fred fred-email
5 sam sam-email
(4 row(s) affected)