Como posso remover linhas duplicadas?
Qual é a melhor maneira de remover linhas duplicadas de uma linha bastante grande SQL Server
quadro (ou seja, 300 000 + linhas)?
as linhas, é claro, não serão duplicados perfeitos por causa da existência do campo de identidade RowID
.
MyTable
RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null
30 answers
Assumindo que não há nulls, você GROUP BY
as colunas únicas, e SELECT
o MIN (or MAX)
RowId como a linha a manter. Depois, apaga tudo o que não tinha identificação de linha.
DELETE FROM MyTable
LEFT OUTER JOIN (
SELECT MIN(RowId) as RowId, Col1, Col2, Col3
FROM MyTable
GROUP BY Col1, Col2, Col3
) as KeepRows ON
MyTable.RowId = KeepRows.RowId
WHERE
KeepRows.RowId IS NULL
No caso de ter um GUID em vez de um inteiro, pode substituir
MIN(RowId)
Com
CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))
Outra maneira possível de fazer isto é
;
--Ensure that any immediately preceding statement is terminated with a semicolon above
WITH cte
AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3
ORDER BY ( SELECT 0)) RN
FROM #MyTable)
DELETE FROM cte
WHERE RN > 1;
Estou a usar {[[1]} acima porque é arbitrário qual a linha a preservar em caso de empate.
Para preservar a última ordem em RowID
por exemplo, pode usar ORDER BY RowID DESC
Planos De Execução
O plano de execução para isso é muitas vezes mais simples e eficiente do que na resposta aceita, pois não requer a auto-adesão. Isto nem sempre é ... caso no entanto. Um lugar onde a soluçãoGROUP BY
pode ser preferida são as situações em que um agregado de hash seria escolhido preferencialmente a um agregado de fluxo.
A solução ROW_NUMBER
dará sempre praticamente o mesmo plano, enquanto que a estratégia GROUP BY
é mais flexível.
Os factores que poderiam favorecer a abordagem agregada de hash seriam
- não existe um índice útil nas colunas de particionamento
- relativamente menos grupos com relativamente mais duplicados em cada grupo
Em versões extremas deste segundo caso (se existirem muito poucos grupos com muitos duplicados em cada um), também se poderia considerar inserir simplesmente as linhas para manter numa nova tabela, então TRUNCATE
-ing o original e copiá-las de volta para minimizar o registo em comparação com a remoção de uma proporção muito elevada das linhas.
Há um bom artigo sobre Remover Duplicados no site de suporte da Microsoft. É muito conservador-eles têm você fazer tudo em etapas separadas - mas deve funcionar bem contra mesas grandes.
Já usei auto-juntas para fazer isto no passado, embora possa ser pretensioso com uma cláusula:DELETE dupes
FROM MyTable dupes, MyTable fullTable
WHERE dupes.dupField = fullTable.dupField
AND dupes.secondDupField = fullTable.secondDupField
AND dupes.uniqueField > fullTable.uniqueField
A seguinte pesquisa é útil para remover linhas duplicadas. A tabela neste exemplo tem ID
como uma coluna de identidade e as colunas que têm dados duplicados são Column1
, Column2
e Column3
DELETE FROM TableName
WHERE ID NOT IN (SELECT MAX(ID)
FROM TableName
GROUP BY Column1,
Column2,
Column3
/*Even if ID is not null-able SQL Server treats MAX(ID) as potentially
nullable. Because of semantics of NOT IN (NULL) including the clause
below can simplify the plan*/
HAVING MAX(ID) IS NOT NULL)
O seguinte programa mostra a utilização de GROUP BY
, HAVING
, ORDER BY
em uma consulta, e retorna os resultados com coluna duplicada e sua contagem.
SELECT YourColumnName,
COUNT(*) TotalCount
FROM YourTableName
GROUP BY YourColumnName
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
delete t1
from table t1, table t2
where t1.columnA = t2.columnA
and t1.rowid>t2.rowid
Postgres:
delete
from table t1
using table t2
where t1.columnA = t2.columnA
and t1.rowid > t2.rowid
DELETE LU
FROM (SELECT *,
Row_number()
OVER (
partition BY col1, col1, col3
ORDER BY rowid DESC) [Row]
FROM mytable) LU
WHERE [row] > 1
Isto irá apagar as linhas duplicadas, excepto a primeira linha
DELETE
FROM
Mytable
WHERE
RowID NOT IN (
SELECT
MIN(RowID)
FROM
Mytable
GROUP BY
Col1,
Col2,
Col3
)
(http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server)
Preferia que o CTE removesse linhas duplicadas da tabela do servidor SQL
Recomendo vivamente que siga este artigo:: http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/
Mantendo o original
WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN
FROM MyTable
)
DELETE FROM CTE WHERE RN<>1
Sem manter o original
WITH CTE AS
(SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3)
FROM MyTable)
DELETE CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)
Rápida e suja para apagar as linhas duplicadas exactas (para tabelas pequenas):
select distinct * into t2 from t1;
delete from t1;
insert into t1 select * from t2;
drop table t2;
Eu prefiro a subquery\tendo conta (*) > 1 solução para a junção interna porque eu achei mais fácil de ler e foi muito fácil de transformar em uma instrução selecionada para verificar o que seria excluído antes de executá-lo.
--DELETE FROM table1
--WHERE id IN (
SELECT MIN(id) FROM table1
GROUP BY col1, col2, col3
-- could add a WHERE clause here to further filter
HAVING count(*) > 1
--)
SELECT DISTINCT *
INTO tempdb.dbo.tmpTable
FROM myTable
TRUNCATE TABLE myTable
INSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable
DROP TABLE tempdb.dbo.tmpTable
Para Obter As Linhas Duplicadas:
SELECT
name, email, COUNT(*)
FROM
users
GROUP BY
name, email
HAVING COUNT(*) > 1
Para apagar as linhas duplicadas:
DELETE users
WHERE rowid NOT IN
SELECT MIN(rowid)
FROM users
GROUP BY name, email);
Mais uma solução fácil pode ser encontrada no link colado aqui. Este é fácil de entender e parece ser eficaz para a maioria dos problemas semelhantes. É para o servidor SQL, mas o conceito usado é mais do que aceitável.
Aqui estão as partes relevantes da página ligada:
Considere estes dados:
EMPLOYEE_ID ATTENDANCE_DATE
A001 2011-01-01
A001 2011-01-01
A002 2011-01-01
A002 2011-01-01
A002 2011-01-01
A003 2011-01-01
Então como podemos apagar esses dados duplicados?
Em primeiro Lugar, inserir uma coluna de identidade nesse quadro, utilizando o seguinte: código:
ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)
Use o seguinte código para o resolver:
DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)
begin transaction
-- create temp table with identical structure as source table
Select * Into #temp From tableName Where 1 = 2
-- insert distinct values into temp
insert into #temp
select distinct *
from tableName
-- delete from source
delete from tableName
-- insert into source from temp
insert into tableName
select *
from #temp
rollback transaction
-- if this works, change rollback to commit and execute again to keep you changes!!
PS: ao trabalhar em coisas como esta eu sempre uso uma transação, isso não só garante que tudo é executado como um todo, mas também me permite testar sem arriscar nada. Mas é claro que devias ter um reforço, só para ter a certeza...
Esta consulta mostrou um bom desempenho para mim:
DELETE tbl
FROM
MyTable tbl
WHERE
EXISTS (
SELECT
*
FROM
MyTable tbl2
WHERE
tbl2.SameValue = tbl.SameValue
AND tbl.IdUniqueValue < tbl2.IdUniqueValue
)
Suprimiu 1m de linhas em pouco mais de 30sec de uma tabela de 2m (50% duplicados)
Usar o etc. A idéia é juntar-se em uma ou mais colunas que formam um registro duplicado e, em seguida, remover o que você quiser:
;with cte as (
select
min(PrimaryKey) as PrimaryKey
UniqueColumn1,
UniqueColumn2
from dbo.DuplicatesTable
group by
UniqueColumn1, UniqueColumn1
having count(*) > 1
)
delete d
from dbo.DuplicatesTable d
inner join cte on
d.PrimaryKey > cte.PrimaryKey and
d.UniqueColumn1 = cte.UniqueColumn1 and
d.UniqueColumn2 = cte.UniqueColumn2;
Aqui está outro bom artigo sobreRemover Duplicados .
It discusses why its hard: " SQL is based on relational algebra, and duplicates cannot occur in relational algebra, because duplicates are not allowed in a set."
A solução da mesa temporária, e dois exemplos mysql.
No futuro, vai impedi-lo a nível de uma base de dados ou numa perspectiva de Aplicação. Eu sugeriria o nível da base de dados porque a sua base de dados deve ser responsável pela manutenção da integridade referencial, os desenvolvedores apenas causarão problemas;)DELETE FROM myTable WHERE RowID IN (
SELECT MIN(RowID) AS IDNo FROM myTable
GROUP BY Col1, Col2, Col3
HAVING COUNT(*) = 2 )
A outra maneira é Criar uma nova tabela com os mesmos campos e com um índice único. Então Mova todos os dados da tabela antiga para a tabela nova . Ignorar automaticamente o servidor SQL (existe também uma opção sobre o que fazer se houver um valor duplicado: ignorar, interromper ou sth) duplicar os valores. Então temos a mesma mesa sem linhas duplicadas. Se não quiser um índice único, depois dos dados de transferência, pode largá-lo .
Especialmente para maiores tabelas você pode usar o pacote DTS (SSIS package to import/export data) a fim de transferir todos os dados rapidamente para a sua nova tabela indexada unicamente. Para 7 milhões de filas é preciso apenas alguns minutos.
Utilize este
WITH tblTemp as
(
SELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)
As RowNumber,* FROM <table_name>
)
DELETE FROM tblTemp where RowNumber >1
testing
e os nomes das colunasempno,empname
DELETE FROM testing WHERE empno not IN (SELECT empno FROM (SELECT empno, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno)
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
or empname not in
(select empname from (select empname,row_number() over(PARTITION BY empno ORDER BY empno)
AS [ItemNumber] FROM testing) a WHERE ItemNumber > 1)
Criar uma nova tabela em branco com a mesma estrutura
-
Executar uma pesquisa como esta
INSERT INTO tc_category1 SELECT * FROM tc_category GROUP BY category_id, application_id HAVING count(*) > 1
-
Execute então esta consulta
INSERT INTO tc_category1 SELECT * FROM tc_category GROUP BY category_id, application_id HAVING count(*) = 1
Esta é a maneira mais fácil de apagar o registo duplicado
DELETE FROM tblemp WHERE id IN
(
SELECT MIN(id) FROM tblemp
GROUP BY title HAVING COUNT(id)>1
)
Http://askme.indianyouth.info/details/how-to-dumplicate-record-from-table-in-using-sql-105
SET ROWCOUNT 1 -- or set to number of rows to be deleted
delete from myTable where RowId = DuplicatedID
SET ROWCOUNT 0
Não sei se funcionaria bem, mas acho que se podia escrever um gatilho para fazer cumprir isto, mesmo que não o conseguisses fazer directamente com um índice. Algo do género:A partir do nível de Aplicação (infelizmente). Concordo que a maneira adequada de evitar a duplicação é ao nível da base de dados através do uso de um índice único, mas no servidor SQL 2005, um índice é permitido ser apenas 900 bytes, e meu campo varchar(2048) sopra isso longe.
-- given a table stories(story_id int not null primary key, story varchar(max) not null)
CREATE TRIGGER prevent_plagiarism
ON stories
after INSERT, UPDATE
AS
DECLARE @cnt AS INT
SELECT @cnt = Count(*)
FROM stories
INNER JOIN inserted
ON ( stories.story = inserted.story
AND stories.story_id != inserted.story_id )
IF @cnt > 0
BEGIN
RAISERROR('plagiarism detected',16,1)
ROLLBACK TRANSACTION
END
Também, varchar (2048) parece-me suspeito (algumas coisas na vida são 2048 bytes, mas é bastante incomum); não deveria realmente ser varchar(max)?
DELETE
FROM
table_name T1
WHERE
rowid > (
SELECT
min(rowid)
FROM
table_name T2
WHERE
T1.column_name = T2.column_name
);
CREATE TABLE car(Id int identity(1,1), PersonId int, CarId int)
INSERT INTO car(PersonId,CarId)
VALUES(1,2),(1,3),(1,2),(2,4)
--SELECT * FROM car
;WITH CTE as(
SELECT ROW_NUMBER() over (PARTITION BY personid,carid order by personid,carid) as rn,Id,PersonID,CarId from car)
DELETE FROM car where Id in(SELECT Id FROM CTE WHERE rn>1)
DELETE
FROM MyTable
WHERE NOT EXISTS (
SELECT min(RowID)
FROM Mytable
WHERE (SELECT RowID
FROM Mytable
GROUP BY Col1, Col2, Col3
))
);
DELETE A
FROM TABLE A,
TABLE B
WHERE A.COL1 = B.COL1
AND A.COL2 = B.COL2
AND A.UNIQUEFIELD > B.UNIQUEFIELD