Come eliminare le righe duplicate in SQL Server?

Come posso delete duplicate rows cui non esiste un unique row id ?

Il mio tavolo è

 col1 col2 col3 col4 col5 col6 col7 john 1 1 1 1 1 1 john 1 1 1 1 1 1 sally 2 2 2 2 2 2 sally 2 2 2 2 2 2 

Voglio essere lasciato con il seguente dopo la rimozione duplicata:

 john 1 1 1 1 1 1 sally 2 2 2 2 2 2 

Ho provato alcune domande ma penso che dipendano da un id di riga in quanto non ottengo il risultato desiderato. Per esempio:

 DELETE FROM table WHERE col1 IN ( SELECT id FROM table GROUP BY id HAVING ( COUNT(col1) > 1 ) ) 

Mi piacciono i CTE e il ROW_NUMBER poiché i due combinati ci permettono di vedere quali file sono cancellate (o aggiornate), quindi basta cambiare il DELETE FROM CTE... in SELECT * FROM CTE :

 WITH CTE AS( SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7], RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1) FROM dbo.Table1 ) DELETE FROM CTE WHERE RN > 1 

DEMO (il risultato è diverso, presumo che sia dovuto a un errore di battitura da parte dell’utente)

 COL1 COL2 COL3 COL4 COL5 COL6 COL7 john 1 1 1 1 1 1 sally 2 2 2 2 2 2 

Questo esempio determina i duplicati di una colonna singola col1 causa della PARTITION BY col1 . Se vuoi includere più colonne, aggiungili semplicemente a PARTITION BY :

 ROW_NUMBER()OVER(PARTITION BY Col1, Col2, ... ORDER BY OrderColumn) 

Preferirei CTE per l’eliminazione di righe duplicate dalla tabella del server sql

consigliamo vivamente di seguire questo articolo: http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/

mantenendo originale

 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 

senza mantenere originale

 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) 

Senza usare CTE e ROW_NUMBER() puoi semplicemente cancellare i record semplicemente usando group by con la funzione MAX qui è ed esempio

 DELETE FROM MyDuplicateTable WHERE ID NOT IN ( SELECT MAX(ID) FROM MyDuplicateTable GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3) 
 DELETE from search where id not in ( select min(id) from search group by url having count(*)=1 union SELECT min(id) FROM search group by url having count(*) > 1 ) 

Microsoft ha una guida molto chiara su come rimuovere i duplicati. Dai un’occhiata a http://support.microsoft.com/kb/139444

In breve, ecco il modo più semplice per eliminare i duplicati quando hai solo poche righe da eliminare:

 SET rowcount 1; DELETE FROM t1 WHERE myprimarykey=1; 

myprimarykey è l’identificatore della riga.

Ho impostato il conteggio delle righe su 1 perché avevo solo due righe duplicate. Se avessi avuto 3 righe duplicate avrei impostato il conteggio delle righe su 2 in modo che cancelli i primi due che vede e ne lascia uno solo nella tabella t1.

Spero che aiuti chiunque

Se non hai riferimenti, come chiavi esterne, puoi farlo. Lo faccio molto quando collaudo le dimostrazioni di concetto e i dati del test vengono duplicati.

SELECT DISTINCT [col1], [col2], [col3], [col4], [col5], [col6], [col7]

INTO [newTable]

;

Vai nell’Explorer oggetti ed elimina la vecchia tabella.

Rinominare la nuova tabella con il nome del vecchio tavolo.

Si prega di vedere anche la modalità di cancellazione qui sotto.

 Declare @table table (col1 varchar(10),col2 int,col3 int, col4 int, col5 int, col6 int, col7 int) Insert into @table values ('john',1,1,1,1,1,1), ('john',1,1,1,1,1,1), ('sally',2,2,2,2,2,2), ('sally',2,2,2,2,2,2) 

Creata una tabella di esempio chiamata @table e caricata con dati dati.

inserisci la descrizione dell'immagine qui

 Delete aliasName from ( Select *, ROW_NUMBER() over (Partition by col1,col2,col3,col4,col5,col6,col7 order by col1) as rowNumber From @table) aliasName Where rowNumber > 1 Select * from @table 

inserisci la descrizione dell'immagine qui

Nota: se si assegnano tutte le colonne nella Partition by parte, l’ order by non ha molto significato.

Lo so, la domanda è stata posta tre anni fa e la mia risposta è un’altra versione di ciò che Tim ha pubblicato, ma pubblicare solo incassare è utile per chiunque.

 with myCTE as ( select productName,ROW_NUMBER() over(PARTITION BY productName order by slno) as Duplicate from productDetails ) Delete from myCTE where Duplicate>1 
 -- this query will keep only one instance of a duplicate record. ;WITH cte AS (SELECT ROW_NUMBER() OVER (PARTITION BY col1, col2, col3-- based on what? --can be multiple columns ORDER BY ( SELECT 0)) RN FROM Mytable) delete FROM cte WHERE RN > 1 

Con riferimento a https://support.microsoft.com/en-us/help/139444/how-to-remove-duplicate-rows-from-a-table-in-sql-server

L’idea di rimuovere duplicati comporta

  • a) Protezione di quelle righe che non sono duplicate
  • b) Conservare una delle tante righe che si sono qualificate come duplicate.

Passo dopo passo

  • 1) Identifica innanzitutto le righe che soddisfano la definizione di duplicato e inseriscile nella tabella temporanea, ad esempio #tableAll.
  • 2) Selezionare le righe non duplicate (a riga singola) o distinte nella tabella temporanea dire #tableUnique.
  • 3) Elimina dalla tabella sorgente aggiungendo #tableAll per eliminare i duplicati.
  • 4) Inserire nella tabella di origine tutte le righe da #tableUnique.
  • 5) Cancella #tableAll e #tableUnique

Se hai la possibilità di aggiungere temporaneamente una colonna alla tabella, questa è stata una soluzione che ha funzionato per me:

 ALTER TABLE dbo.DUPPEDTABLE ADD RowID INT NOT NULL IDENTITY(1,1) 

Quindi eseguire un DELETE utilizzando una combinazione di MIN e GROUP BY

 DELETE b FROM dbo.DUPPEDTABLE b WHERE b.RowID NOT IN ( SELECT MIN(RowID) AS RowID FROM dbo.DUPPEDTABLE a WITH (NOLOCK) GROUP BY a.ITEM_NUMBER, a.CHARACTERISTIC, a.INTVALUE, a.FLOATVALUE, a.STRINGVALUE ); 

Verificare che il DELETE ha eseguito correttamente:

 SELECT a.ITEM_NUMBER, a.CHARACTERISTIC, a.INTVALUE, a.FLOATVALUE, a.STRINGVALUE, COUNT(*)--MIN(RowID) AS RowID FROM dbo.DUPPEDTABLE a WITH (NOLOCK) GROUP BY a.ITEM_NUMBER, a.CHARACTERISTIC, a.INTVALUE, a.FLOATVALUE, a.STRINGVALUE ORDER BY COUNT(*) DESC 

Il risultato non dovrebbe avere righe con un conteggio maggiore di 1. Infine, rimuovere la colonna rowid:

 ALTER TABLE dbo.DUPPEDTABLE DROP COLUMN RowID; 

Un altro modo per rimuovere le righe pubblicizzate senza perdere informazioni in un solo passaggio è come seguire:

 delete from dublicated_table t1 (nolock) join ( select t2.dublicated_field , min(len(t2.field_kept)) as min_field_kept from dublicated_table t2 (nolock) group by t2.dublicated_field having COUNT(*)>1 ) t3 on t1.dublicated_field=t3.dublicated_field and len(t1.field_kept)=t3.min_field_kept 

Dopo aver provato la soluzione suggerita sopra, questo funziona per piccoli tavoli medi. Posso suggerire questa soluzione per tavoli molto grandi. poiché viene eseguito in iterazioni.

  1. Eliminare tutte le visualizzazioni di dipendenza di LargeSourceTable
  2. puoi trovare le dipendenze usando sql managment studio, fai clic destro sulla tabella e fai clic su “Visualizza dipendenze”
  3. Rinominare la tabella:
  4. sp_rename 'LargeSourceTable', 'LargeSourceTable_Temp'; GO
  5. Crea nuovamente la LargeSourceTable , ma ora aggiungi una chiave primaria con tutte le colonne che definiscono le duplicazioni aggiungi WITH (IGNORE_DUP_KEY = ON)
  6. Per esempio:

    CREATE TABLE [dbo].[LargeSourceTable] ( ID int IDENTITY(1,1), [CreateDate] DATETIME CONSTRAINT [DF_LargeSourceTable_CreateDate] DEFAULT (getdate()) NOT NULL, [Column1] CHAR (36) NOT NULL, [Column2] NVARCHAR (100) NOT NULL, [Column3] CHAR (36) NOT NULL, PRIMARY KEY (Column1, Column2) WITH (IGNORE_DUP_KEY = ON) ); GO

  7. Crea nuovamente le viste che hai eliminato in primo luogo per la nuova tabella creata

  8. Ora, esegui il seguente script sql, vedrai i risultati in 1.000.000 di righe per pagina, puoi cambiare il numero di riga per pagina per vedere i risultati più spesso.

  9. Nota che ho impostato l’ IDENTITY_INSERT on e off perché una delle colonne contiene l’ID incrementale automatico, che sto anche copiando

SET IDENTITY_INSERT LargeSourceTable ON DECLARE @PageNumber AS INT, @RowspPage AS INT DECLARE @TotalRows AS INT declare @dt varchar(19) SET @PageNumber = 0 SET @RowspPage = 1000000 select @TotalRows = count (*) from LargeSourceTable_TEMP

 While ((@PageNumber - 1) * @RowspPage < @TotalRows ) Begin begin transaction tran_inner ; with cte as ( SELECT * FROM LargeSourceTable_TEMP ORDER BY ID OFFSET ((@PageNumber) * @RowspPage) ROWS FETCH NEXT @RowspPage ROWS ONLY ) INSERT INTO LargeSourceTable ( ID ,[CreateDate] ,[Column1] ,[Column2] ,[Column3] ) select ID ,[CreateDate] ,[Column1] ,[Column2] ,[Column3] from cte commit transaction tran_inner PRINT 'Page: ' + convert(varchar(10), @PageNumber) PRINT 'Transfered: ' + convert(varchar(20), @PageNumber * @RowspPage) PRINT 'Of: ' + convert(varchar(20), @TotalRows) SELECT @dt = convert(varchar(19), getdate(), 121) RAISERROR('Inserted on: %s', 0, 1, @dt) WITH NOWAIT SET @PageNumber = @PageNumber + 1 End 

SET IDENTITY_INSERT LargeSourceTable OFF

Oh wow, mi sento così stupido pronto tutte queste risposte, sono come la risposta di esperti con tutti i CTE e temp table e così via.

E tutto ciò che ho fatto per farlo funzionare è stato semplicemente aggregato alla colonna ID usando MAX.

 DELETE FROM table WHERE col1 IN ( SELECT MAX(id) FROM table GROUP BY id HAVING ( COUNT(col1) > 1 ) ) 

NOTA: potrebbe essere necessario eseguirlo più volte per rimuovere duplicati in quanto eliminerà solo un set di righe duplicate alla volta.

Se riesci a trovare il numero di righe duplicate, ad esempio hai n righe duplicate, quindi usa questo comando

 SET rowcount n-1 DELETE FROM your_table WHERE (spacial condition) 

per maggiori informazioni suggerisco questo