¿Cómo consultar filas que tienen los mismos valores para dos columnas?
Es fácil encontrar duplicados con un campo:
SELECT email, COUNT(email)
FROM users
GROUP BY email
HAVING COUNT(email) > 1
Entonces si tenemos una mesa
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 lo mismo email.
Sin embargo, lo que quiero es obtener duplicados con el mismo email y name .
Es decir, quiero obtener "Tom", "Tom".
La razón por la que necesito esto: cometí un error y permití insertar valores nameduplicados email. Ahora necesito eliminar/cambiar los duplicados, así que primero necesito encontrarlos .
SELECT
name, email, COUNT(*)
FROM
users
GROUP BY
name, email
HAVING
COUNT(*) > 1
Simplemente agrupe en ambas columnas.
Nota: el estándar ANSI anterior es tener todas las columnas no agregadas en GROUP BY, pero esto ha cambiado con la idea de "dependencia funcional" :
En la teoría de 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 atributos en una relación.
El soporte no es consistente:
- PostgreSQL reciente lo admite .
- SQL Server (como en SQL Server 2017) todavía requiere todas las columnas no agregadas en GROUP BY.
- MySQL es impredecible y necesitas
sql_mode=only_full_group_by:- GRUPO POR nombre ORDENAR mostrando resultados incorrectos ;
- Cuál es la función agregada menos costosa en ausencia de ANY() (ver comentarios en la respuesta aceptada).
- Oracle no es lo suficientemente popular (advertencia: humor, no sé nada de Oracle).
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
PRODUCCIÓN:
name email CountOf
---------- ----------- -----------
John John-email 2
sam sam-email 2
(2 row(s) affected)
Si desea los ID de los duplicados, utilice 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
PRODUCCIÓN:
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 intente:
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
PRODUCCIÓN:
id name email
----------- ---------- --------------
1 John John-email
3 fred John-email
4 fred fred-email
5 sam sam-email
(4 row(s) affected)
SELECT name, email
FROM users
GROUP BY name, email
HAVING ( COUNT(*) > 1 )
Si desea eliminar los duplicados, aquí tiene una forma mucho más sencilla de hacerlo que tener que buscar filas pares/impares en una subselección triple:
SELECT id, name, email
FROM users u, users u2
WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id
Y así eliminar:
DELETE FROM users
WHERE id IN (
SELECT id/*, name, email*/
FROM users u, users u2
WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id
)
Mucho más fácil de leer y entender en mi humilde opinión.
Nota: El único problema es que debe ejecutar la solicitud hasta que no se eliminen filas, ya que solo elimina 1 de cada duplicado cada vez.
