Procurar valores duplicados numa tabela SQL
SELECT name, COUNT(email)
FROM users
GROUP BY email
HAVING COUNT(email) > 1
Por isso, se tivermos uma mesa ...
ID NAME EMAIL
1 John [email protected]
2 Sam [email protected]
3 Tom [email protected]
4 Bob [email protected]
5 Tom [email protected]
[8] esta consulta nos dará John, Sam, Tom, Tom, porque todos eles têm o mesmo email
.
No entanto, o que eu quero é obter duplicados com o mesmo email
e name
.
Isto é, quero chamar "Tom", "Tom".
a razão pela qual Preciso disto: cometi um erro e permiti-me inserir valores duplicados name
e email
. Agora eu preciso remover/mudar os duplicados, por isso preciso de Os encontrar primeiro.
30 answers
SELECT
name, email, COUNT(*)
FROM
users
GROUP BY
name, email
HAVING
COUNT(*) > 1
Agrupar-se em ambas as colunas.
Nota: a norma ANSI mais antiga é ter todas as colunas não agregadas no grupo, mas isto mudou com a ideia de "dependência funcional":
Na teoria da base de dados relacional, uma dependência funcional é uma restrição entre dois conjuntos de atributos em uma relação de uma base de dados. Em outras palavras, dependência funcional é uma restrição que descreve a relação entre atributos em um relacao.
O suporte não é consistente:
- o PostgreSQL recente suporta .
- O servidor SQL (como no servidor SQL 2017) ainda necessita de todas as colunas não agregadas do grupo por. O MySQL é imprevisível e tu precisas dele.:
- agrupar por ordem do lname, mostrando os resultados errados;
- que é a função agregada menos dispendiosa na ausência de qualquer() (ver comentários em resposta).
Tenta isto:
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
Resultado:
name email CountOf
---------- ----------- -----------
John John-email 2
sam sam-email 2
(2 row(s) affected)
Se quiser as identidades dos dups, use isto:
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
Resultado:
id name email
----------- ---------- ------------
1 John John-email
2 John John-email
5 sam sam-email
6 sam sam-email
(4 row(s) affected)
Para apagar os duplicados tente:
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
Resultado:
id name email
----------- ---------- --------------
1 John John-email
3 fred John-email
4 fred fred-email
5 sam sam-email
(4 row(s) affected)
Tenta isto:
SELECT name, email
FROM users
GROUP BY name, email
HAVING ( COUNT(*) > 1 )
Se quiser apagar os duplicados, aqui está uma forma muito mais simples de o fazer do que ter de encontrar linhas par / ímpares numa sub-selecção tripla:
SELECT id, name, email
FROM users u, users u2
WHERE u.name = u2.name AND u.email = u2.email AND u.id > u2.id
E assim apagar:
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
)
Muito mais fácil de ler e entender IMHO
Nota: o único problema é que você tem que executar o pedido até que não haja nenhuma linha apagada, uma vez que você apaga apenas 1 de cada duplicado de cada vez
Tenta o seguinte:
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
SELECT name, email
FROM users
WHERE email in
(SELECT email FROM users
GROUP BY email
HAVING COUNT(*)>1)
SELECT GROUP_CONCAT( id )
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
Tenta 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
Isto selecciona / apaga todos os registos duplicados, excepto um registo de cada grupo de duplicados. Assim, a delete deixa todos os registros únicos + um registro de cada grupo de duplicados.
Seleccione os duplicados:
SELECT *
FROM table
WHERE
id NOT IN (
SELECT MIN(id)
FROM table
GROUP BY column1, column2
);
Apagar duplicados:
DELETE FROM table
WHERE
id NOT IN (
SELECT MIN(id)
FROM table
GROUP BY column1, column2
);
esteja ciente de grandes quantidades de registros, ele pode causar problemas de desempenho.
No caso de trabalhar com a Oracle, este caminho seria preferível:
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);
select name, email
, case
when ROW_NUMBER () over (partition by name, email order by name) > 1 then 'Yes'
else 'No'
end "duplicated ?"
from users
Se deseja ver se há algum duplicado de linhas na sua tabela, eu usei abaixo a 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
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
/
SELECT id, COUNT(id) FROM table1 GROUP BY id HAVING COUNT(id)>1;
Acho que isto vai funcionar correctamente para procurar valores repetidos numa coluna em particular.
Este exemplo encontra todos os alunos com nome duplicado e dob. Os campos que você quer verificar para duplicação vão na cláusula de OVER. Você pode incluir quaisquer outros campos que você quiser na projeção.
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
select id,name,COUNT(*) from user group by Id,Name having COUNT(*)>1
Tão simples como
select COUNT(distinct col_01) from Table_01
Usando o CTE também podemos encontrar um 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
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 bom no seu caso se procurar por duplicados que tenham algum tipo de prefixo ou mudança geral, como por exemplo, novo domínio no correio. então você pode usar o replace () nestas colunas
SELECT * FROM users u where rowid = (select max(rowid) from users u1 where
u.email=u1.email);
SELECT name, email,COUNT(email)
FROM users
WHERE email IN (
SELECT email
FROM users
GROUP BY email
HAVING COUNT(email) > 1)
Se quiser encontrar dados duplicados (por um ou vários critérios) e seleccionar as linhas actuais.
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/
SELECT u.*
FROM users AS u
JOIN (SELECT username, email
FROM users
GROUP BY username, email
HAVING COUNT(*)>1) AS w
ON u.username=w.username AND u.email=w.email
ORDER BY u.email;
Apagar Registos cujos nomes são duplicados
;WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) AS T FROM @YourTable
)
DELETE FROM CTE WHERE T > 1
Para verificar a partir de um registo duplicado numa tabela.
select * from users s
where rowid < any
(select rowid from users k where s.name = k.name and s.email = k.email);
Ou
select * from users s
where rowid not in
(select max(rowid) from users k where s.name = k.name and s.email = k.email);
Apagar o registo duplicado numa tabela.
delete from users s
where rowid < any
(select rowid from users k where s.name = k.name and s.email = k.email);
Ou
delete from users s
where rowid not in
(select max(rowid) from users k where s.name = k.name and s.email = k.email);
SELECT column_name,COUNT(*) FROM TABLE_NAME GROUP BY column1, HAVING COUNT(*) > 1;
Nós podemos usar ter aqui que trabalham em funções agregadas como mostrado abaixo
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
Aqui como dois campos ID_ conta e os dados são usados com a contagem (*). Assim, ele vai dar todos os registros que tem mais de uma vez os mesmos valores em ambas as colunas.
Por alguma razão errámos erradamente em adicionar quaisquer restrições na tabela do servidor SQL e os registos foram inseridos em duplicado em todas as colunas com aplicação front-end. Então podemos usar a consulta abaixo para excluir a consulta duplicada de tabela.
SELECT DISTINCT * INTO #TemNewTable FROM #OriginalTable
TRUNCATE TABLE #OriginalTable
INSERT INTO #OriginalTable SELECT * FROM #TemNewTable
DROP TABLE #TemNewTable
Aqui pegamos todos os registros distintos da tabela orignal e apagamos os registros da tabela original. Mais uma vez, inserimos todos os valores distintos da nova tabela para a tabela original e, em seguida, apagamos nova tabela.
Talvez queiras experimentar isto
SELECT NAME, EMAIL, COUNT(*)
FROM USERS
GROUP BY 1,2
HAVING COUNT(*) > 1
Outra maneira fácil de tentar isto usando a função analítica também:
SELECT * from
(SELECT name, email,
COUNT(name) OVER (PARTITION BY name, email) cnt
FROM users)
WHERE cnt >1;
Pode usar a palavra-chave seleccionada para se livrar dos duplicados. Você também pode filtrar pelo nome e obter todos com esse nome em uma mesa.