sql-server - eliminare - sql distinct




Come posso rimuovere le righe duplicate? (20)

Dal livello di applicazione (sfortunatamente). Sono d'accordo che il modo corretto per evitare la duplicazione è a livello di database attraverso l'uso di un indice univoco, ma in SQL Server 2005, un indice può essere solo 900 byte e il mio campo varchar (2048) lo respinge.

Non so quanto si comporterebbe bene, ma penso che potresti scrivere un trigger per far rispettare questo, anche se non potresti farlo direttamente con un indice. Qualcosa di simile a:

-- 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 

Inoltre, varchar (2048) mi sembra strano (alcune cose nella vita sono 2048 byte, ma è abbastanza raro); dovrebbe davvero non essere varchar (max)?

Qual è il modo migliore per rimuovere le righe duplicate da una tabella SQL Server abbastanza grande (ad esempio 300.000+ righe)?

Le righe, naturalmente, non saranno duplicati perfetti a causa dell'esistenza del campo identità 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

  1. Crea una nuova tabella vuota con la stessa struttura

  2. Esegui una query come questa

    INSERT INTO tc_category1
    SELECT *
    FROM tc_category
    GROUP BY category_id, application_id
    HAVING count(*) > 1
    
  3. Quindi eseguire questa query

    INSERT INTO tc_category1
    SELECT *
    FROM tc_category
    GROUP BY category_id, application_id
    HAVING count(*) = 1
    

C'è un buon articolo sulla rimozione di duplicati sul sito di supporto Microsoft. È piuttosto prudente: ti fanno fare tutto in passaggi separati, ma dovrebbe funzionare bene contro i tavoli di grandi dimensioni.

Ho usato auto-join per farlo in passato, anche se probabilmente potrebbe essere predisposto con una clausola HAVING:

DELETE dupes
FROM MyTable dupes, MyTable fullTable
WHERE dupes.dupField = fullTable.dupField 
AND dupes.secondDupField = fullTable.secondDupField 
AND dupes.uniqueField > fullTable.uniqueField

Ecco un altro buon articolo sulla rimozione di duplicati .

Si discute perché è difficile: " SQL è basato sull'algebra relazionale e non si possono verificare duplicati nell'algebra relazionale, perché i duplicati non sono consentiti in un insieme. "

La soluzione temp table e due esempi mysql.

In futuro lo prevedi a livello di database o dal punto di vista dell'applicazione. Suggerirei il livello del database perché il tuo database dovrebbe essere responsabile del mantenimento dell'integrità referenziale, gli sviluppatori causeranno solo problemi;)


L'altro modo è Creare una nuova tabella con gli stessi campi e con Indice univoco . Quindi sposta tutti i dati dalla vecchia tabella alla nuova tabella . Automaticamente SQL SERVER ignora (c'è anche un'opzione su cosa fare se ci sarà un valore duplicato: ignore, interrupt o sth) valori duplicati. Quindi abbiamo la stessa tabella senza righe duplicate. Se non si desidera l'indice univoco, dopo i dati di trasferimento è possibile rilasciarlo .

Soprattutto per le tabelle più grandi è possibile utilizzare DTS (pacchetto SSIS per importare / esportare dati) per trasferire rapidamente tutti i dati alla nuova tabella indicizzata in modo univoco. Per 7 milioni di righe ci vogliono solo pochi minuti.


La seguente query è utile per eliminare le righe duplicate. La tabella in questo esempio ha ID come colonna Identity e le colonne che hanno dati duplicati sono 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) 

Il seguente script mostra l'utilizzo di GROUP BY , HAVING , ORDER BY in una query e restituisce i risultati con la colonna duplicata e il suo conteggio.

SELECT YourColumnName,
       COUNT(*) TotalCount
FROM   YourTableName
GROUP  BY YourColumnName
HAVING COUNT(*) > 1
ORDER  BY COUNT(*) DESC 

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)

Preferisco la subquery \ avere una soluzione di conteggio (*)> 1 al join interno perché ho trovato più facile leggere ed è stato molto facile trasformarsi in un'istruzione SELECT per verificare cosa sarebbe stato eliminato prima di eseguirlo.

--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
--)



Un altro modo possibile per farlo è

; 

--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;

Sto usando ORDER BY (SELECT 0) sopra poiché è arbitrario quale riga conservare in caso di pareggio.

Ad esempio, per conservare l'ultimo nell'ordine RowID è possibile utilizzare ORDER BY RowID DESC

Piani di esecuzione

Il piano di esecuzione per questo è spesso più semplice e più efficiente di quello nella risposta accettata in quanto non richiede l'auto join.

Tuttavia, questo non è sempre il caso. Un luogo in cui la soluzione GROUP BY può essere preferita è quella in cui un aggregato hash viene scelto preferibilmente a un aggregato di flusso.

La soluzione ROW_NUMBER darà sempre lo stesso piano mentre la strategia GROUP BY è più flessibile.

I fattori che potrebbero favorire l'approccio aggregato dell'hash sarebbero

  • Nessun indice utile sulle colonne di partizionamento
  • relativamente meno gruppi con relativamente più duplicati in ciascun gruppo

Nelle versioni estreme di questo secondo caso (se ci sono pochissimi gruppi con molti duplicati in ciascuno) si potrebbe anche considerare semplicemente l'inserimento delle righe da tenere in una nuova tabella, quindi TRUNCATE l'originale e copiarle per minimizzare la registrazione rispetto all'eliminazione una proporzione molto alta delle file.


Un'altra soluzione semplice può essere trovata al link incollato http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server . Questo facile da capire e sembra essere efficace per la maggior parte dei problemi simili. È per SQL Server, ma il concetto utilizzato è più che accettabile.

Ecco le parti rilevanti dalla pagina collegata:

Considera questi dati:

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

Quindi, come possiamo eliminare quei dati duplicati?

Innanzitutto, inserisci una colonna Identity in quella tabella usando il seguente codice:

ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)  

Usa il seguente codice per risolverlo:

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
    FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) 

Usando la query sottostante possiamo cancellare i record duplicati in base alla singola colonna o a più colonne. la query di seguito viene eliminata in base a due colonne. il nome della tabella è: testing e nomi delle colonne empno,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)

Utilizzando CTE. L'idea è di unirsi su una o più colonne che formano un record duplicato e quindi rimuovere qualsiasi cosa tu voglia:

;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;

Voglio vedere in anteprima le righe che stai per rimuovere e mantenere il controllo su quale delle righe duplicate conservare. Vedi http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/

with MYCTE as (
  SELECT ROW_NUMBER() OVER (
    PARTITION BY DuplicateKey1
                ,DuplicateKey2 -- optional
    ORDER BY CreatedAt -- the first row among duplicates will be kept, other rows will be removed
  ) RN
  FROM MyTable
)
DELETE FROM MYCTE
WHERE RN > 1

Vorrei menzionare questo approccio così come può essere utile, e funziona in tutti i server SQL: abbastanza spesso c'è solo uno - due duplicati, e sono noti ID e conteggio dei duplicati. In questo caso:

SET ROWCOUNT 1 -- or set to number of rows to be deleted
delete from myTable where RowId = DuplicatedID
SET ROWCOUNT 0

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
    table_name T1
WHERE
    rowid > (
        SELECT
            min(rowid)
        FROM
            table_name T2
        WHERE
            T1.column_name = T2.column_name
    );

DELETE LU 
FROM   (SELECT *, 
               Row_number() 
                 OVER ( 
                   partition BY col1, col1, col3 
                   ORDER BY rowid DESC) [Row] 
        FROM   mytable) LU 
WHERE  [row] > 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






duplicates