solo - Encontrar valores duplicados en una tabla SQL

sql eliminar registros repetidos dejando solo 1

Question

Es fácil encontrar duplicates con un campo:

SELECT name, COUNT(email) 
FROM users
GROUP BY email
HAVING COUNT(email) > 1

Así que 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á a John, Sam, Tom, Tom porque todos tienen el 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 duplicados de name y email . Ahora necesito eliminar / cambiar los duplicados, así que primero necesito encontrarlos .

Answer 1

¿Cómo podemos contar los valores duplicados? o bien se repite 2 veces o más que 2. solo cuéntelos, no en grupo.

tan simple como

select COUNT(distinct col_01) from Table_01

Answer 2

En caso de que trabajes con Oracle, esta forma sería preferible:

create table my_users(id number, name varchar2(100), email varchar2(100));

insert into my_users values (1, ''John'', ''[email protected]'');
insert into my_users values (2, ''Sam'', ''[email protected]'');
insert into my_users values (3, ''Tom'', ''[email protected]'');
insert into my_users values (4, ''Bob'', ''[email protected]'');
insert into my_users values (5, ''Tom'', ''[email protected]'');

commit;

select *
  from my_users
 where rowid not in (select min(rowid) from my_users group by name, email);

Answer 3

Esto es lo fácil que se me ha ocurrido. Utiliza una expresión de tabla común (CTE) y una ventana de partición (creo que estas características están en SQL 2008 y versiones posteriores).

Este ejemplo encuentra a todos los estudiantes con nombre duplicado y dob. Los campos que desea verificar para la duplicación van en la cláusula OVER. Puede incluir cualquier otro campo que desee en la proyección.

with cte (StudentId, Fname, LName, DOB, RowCnt)
as (
SELECT StudentId, FirstName, LastName, DateOfBirth as DOB, SUM(1) OVER (Partition By FirstName, LastName, DateOfBirth) as RowCnt
FROM tblStudent
)
SELECT * from CTE where RowCnt > 1
ORDER BY DOB, LName

Answer 4

Esto selecciona / borra todos los registros duplicados excepto un registro de cada grupo de duplicados. Por lo tanto, la eliminación deja todos los registros únicos + un registro de cada grupo de duplicados.

Seleccione duplicados:

SELECT *
FROM table
WHERE
    id NOT IN (
        SELECT MIN(id)
        FROM table
        GROUP BY column1, column2
);

Eliminar duplicados:

DELETE FROM table
WHERE
    id NOT IN (
        SELECT MIN(id)
        FROM table
        GROUP BY column1, column2
);

Tenga en cuenta que una gran cantidad de registros puede causar problemas de rendimiento.

Answer 5

Esto también debería funcionar, tal vez intentarlo.

  Select * from Users a
            where EXISTS (Select * from Users b 
                where (     a.name = b.name 
                        OR  a.email = b.email)
                     and a.ID != b.id)

Especialmente bueno en su caso si busca duplicados que tengan algún tipo de prefijo o cambio general como, por ejemplo, un nuevo dominio en el correo. entonces puedes usar replace () en estas columnas

Answer 6

Intenta lo siguiente:

SELECT * FROM
(
    SELECT Id, Name, Age, Comments, Row_Number() OVER(PARTITION BY Name, Age ORDER By Name)
        AS Rank 
        FROM Customers
) AS B WHERE Rank>1

Answer 7

Podemos usar tener aquí que trabaja en funciones agregadas como se muestra a continuación

create table #TableB (id_account int, data int, [date] date)
insert into #TableB values (1 ,-50, ''10/20/2018''),
(1, 20, ''10/09/2018''),
(2 ,-900, ''10/01/2018''),
(1 ,20, ''09/25/2018''),
(1 ,-100, ''08/01/2018'')  

SELECT id_account , data, COUNT(*)
FROM #TableB
GROUP BY id_account , data
HAVING COUNT(id_account) > 1

drop table #TableB

Aquí, como dos campos, id_account y datos se utilizan con Count (*). Por lo tanto, proporcionará todos los registros que tengan más de una vez los mismos valores en ambas columnas.

Por alguna razón nos habíamos equivocado al agregar alguna restricción en la tabla del servidor SQL y los registros se han insertado duplicados en todas las columnas con la aplicación front-end. Luego podemos usar la siguiente consulta para eliminar la consulta duplicada de la tabla.

SELECT DISTINCT * INTO #TemNewTable FROM #OriginalTable
TRUNCATE TABLE #OriginalTable
INSERT INTO #OriginalTable SELECT * FROM #TemNewTable
DROP TABLE #TemNewTable

Aquí hemos tomado todos los registros distintos de la tabla original y hemos eliminado los registros de la tabla original. Nuevamente insertamos todos los valores distintos de la nueva tabla en la tabla original y luego eliminamos la nueva tabla.

Answer 8

Prueba esto:

SELECT name, email
FROM users
GROUP BY name, email
HAVING ( COUNT(*) > 1 )

Answer 9

Si desea buscar datos duplicados (por uno o varios criterios) y seleccione las filas reales.

with MYCTE as (
    SELECT DuplicateKey1
        ,DuplicateKey2 --optional
        ,count(*) X
    FROM MyTable
    group by DuplicateKey1, DuplicateKey2
    having count(*) > 1
) 
SELECT E.*
FROM MyTable E
JOIN MYCTE cte
ON E.DuplicateKey1=cte.DuplicateKey1
    AND E.DuplicateKey2=cte.DuplicateKey2
ORDER BY E.DuplicateKey1, E.DuplicateKey2, CreatedAt

http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/

Answer 10

Si desea eliminar los duplicados, aquí hay una manera mucho más sencilla de hacerlo que tener que encontrar filas pares / impares en una sub-selecció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í, para 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 IMHO

Nota: El único problema es que tiene que ejecutar la solicitud hasta que no haya filas eliminadas, ya que solo elimina 1 de cada duplicado cada vez

Answer 11

Si desea ver si hay filas duplicadas en su tabla, use la siguiente consulta:

create table my_table(id int, name varchar(100), email varchar(100));

insert into my_table values (1, ''shekh'', ''[email protected]'');
insert into my_table values (1, ''shekh'', ''[email protected]'');
insert into my_table values (2, ''Aman'', ''[email protected]'');
insert into my_table values (3, ''Tom'', ''[email protected]'');
insert into my_table values (4, ''Raj'', ''[email protected]'');


Select COUNT(1) As Total_Rows from my_table 
Select Count(1) As Distinct_Rows from ( Select Distinct * from my_table) abc 

Answer 12

Un poco tarde para la fiesta, pero encontré una solución muy buena para encontrar todas las identificaciones duplicadas:

SELECT GROUP_CONCAT( id )
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

Answer 13

prueba este código

WITH CTE AS

( SELECT Id, Name, Age, Comments, RN = ROW_NUMBER()OVER(PARTITION BY Name,Age ORDER BY ccn)
FROM ccnmaster )
select * from CTE 

Answer 14

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 que los IDs de los dups utilices 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 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

SALIDA:

id          name       email
----------- ---------- --------------
1           John       John-email
3           fred       John-email
4           fred       fred-email
5           sam        sam-email

(4 row(s) affected)

Answer 15

SELECT column_name,COUNT(*) FROM TABLE_NAME GROUP BY column1, HAVING COUNT(*) > 1;

Answer 16

SELECT id, COUNT(id) FROM table1 GROUP BY id HAVING COUNT(id)>1;

Creo que esto funcionará correctamente para buscar valores repetidos en una columna en particular.

Answer 17

Cómo obtener registro duplicado en tabla

SELECCIONAR CUENTA (Código), Código DE Empleados DONDE Estado = 1 GRUPO POR Código QUE TIENE CUENTA (Código)> 1

Answer 18

Usando CTE también podemos encontrar valor duplicado como este

with MyCTE
as
(
select Name,EmailId,ROW_NUMBER() over(PARTITION BY EmailId order by id) as Duplicate from [Employees]

)
select * from MyCTE where Duplicate>1

Answer 19

 SELECT name, email 
    FROM users
    WHERE email in
    (SELECT email FROM users
    GROUP BY email 
    HAVING COUNT(*)>1)

Answer 20

 select emp.ename, emp.empno, dept.loc 
          from emp
 inner join dept 
          on dept.deptno=emp.deptno
 inner join
    (select ename, count(*) from
    emp
    group by ename, deptno
    having count(*) > 1)
 t on emp.ename=t.ename order by emp.ename
/

Answer 21

SELECT
    name, email, COUNT(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

Simplemente agrupe en ambas columnas.

Nota: el estándar ANSI más antiguo es tener todas las columnas no agregadas en el GRUPO POR, 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 apoyo no es consistente:

• PostgreSQL reciente lo soporta .
• SQL Server (como en SQL Server 2017) todavía requiere todas las columnas no agregadas en el GRUPO POR.
• MySQL es impredecible y necesita sql_mode=only_full_group_by :
  • GRUPO POR lname ORDER BY mostrando resultados incorrectos ;
  • Cuál es la función agregada menos costosa en ausencia de ANY () (vea los comentarios en la respuesta aceptada).
• Oracle no es lo suficientemente convencional (advertencia: humor, no sé sobre Oracle).

Answer 22

SELECT
  FirstName, LastName, MobileNo, COUNT(1) as CNT 
FROM        
  CUSTOMER
GROUP BY
  FirstName, LastName, MobileNo 
HAVING
  COUNT(1) > 1;

Answer 23

SELECT * FROM users u where rowid = (select max(rowid) from users u1 where
u.email=u1.email);

Answer 24

select id,name,COUNT(*) from India group by Id,Name having COUNT(*)>1

Answer 25

select name, email
, case 
when ROW_NUMBER () over (partition by name, email order by name) > 1 then ''Yes''
else ''No''
end "duplicated ?"
from users