sql-server - sur - supprimer doublon oracle




Comment puis-je supprimer les lignes en double? (20)

Du niveau de l'application (malheureusement). Je suis d'accord que la bonne façon d'empêcher la duplication est au niveau de la base de données à travers l'utilisation d'un index unique, mais dans SQL Server 2005, un index est autorisé à seulement 900 octets, et mon champ varchar (2048).

Je ne sais pas à quel point cela fonctionnerait, mais je pense que vous pourriez écrire un déclencheur pour l'appliquer, même si vous ne pouviez pas le faire directement avec un index. Quelque chose comme:

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

Aussi, varchar (2048) sonne comme un poisson (certaines choses dans la vie sont 2048 octets, mais c'est assez rare); devrait-il vraiment ne pas être varchar (max)?

Quel est le meilleur moyen de supprimer les lignes en double d'une table SQL Server assez volumineuse (par exemple, 300 000 lignes et plus)?

Les lignes, bien sûr, ne seront pas parfaites en raison de l'existence du champ d'identification RowID .

Ma table

RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null

  1. Créer une nouvelle table vide avec la même structure

  2. Exécuter une requête comme celle-ci

    INSERT INTO tc_category1
    SELECT *
    FROM tc_category
    GROUP BY category_id, application_id
    HAVING count(*) > 1
    
  3. Ensuite, exécutez cette requête

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


Cette requête a montré de très bonnes performances pour moi:

DELETE tbl
FROM
    MyTable tbl
WHERE
    EXISTS (
        SELECT
            *
        FROM
            MyTable tbl2
        WHERE
            tbl2.SameValue = tbl.SameValue
        AND tbl.IdUniqueValue < tbl2.IdUniqueValue
    )

il a supprimé 1M lignes en un peu plus de 30 secondes à partir d'une table de 2M (50% de doublons)


En utilisant la requête ci-dessous, nous pouvons supprimer les enregistrements en double en fonction de la colonne unique ou de la colonne multiple. La requête ci-dessous est en cours de suppression sur la base de deux colonnes. le nom de la table est: testing et les noms de colonnes 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)

Encore une autre solution facile peut être trouvée sur le lien collé http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server . Celui-ci est facile à saisir et semble être efficace pour la plupart des problèmes similaires. C'est pour SQL Server cependant mais le concept utilisé est plus qu'acceptable.

Voici les parties pertinentes de la page liée:

Considérez ces données:

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

Alors, comment pouvons-nous supprimer ces données en double?

Tout d'abord, insérez une colonne d'identité dans cette table en utilisant le code suivant:

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

Utilisez le code suivant pour le résoudre:

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

J'avais une table où j'avais besoin de préserver les lignes non dupliquées. Je ne suis pas sûr de la vitesse ou de l'efficacité.

DELETE FROM myTable WHERE RowID IN (
  SELECT MIN(RowID) AS IDNo FROM myTable
  GROUP BY Col1, Col2, Col3
  HAVING COUNT(*) = 2 )

Je pensais partager ma solution, car cela fonctionne dans des circonstances particulières. Dans mon cas, la table avec des valeurs en double n'a pas de clé étrangère (parce que les valeurs ont été dupliquées à partir d'une autre base de données).

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: quand je travaille sur des choses comme ça, j'utilise toujours une transaction, non seulement cela assure que tout est exécuté dans son ensemble, mais cela me permet également de tester sans risquer quoi que ce soit. Mais bien sûr, vous devriez prendre une sauvegarde de toute façon juste pour être sûr ...


Je préfère la sous-requête ayant la solution count (*)> 1 à la jointure interne car je l'ai trouvé plus facile à lire et il était très facile de transformer une instruction SELECT pour vérifier ce qui serait supprimé avant de l'exécuter.

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

Je voudrais mentionner cette approche aussi bien que cela peut être utile, et fonctionne dans tous les serveurs SQL: Assez souvent, il n'y a qu'un - deux doublons, et les ID et le nombre de doublons sont connus. Dans ce cas:

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

La requête suivante est utile pour supprimer les lignes en double. La table dans cet exemple a ID tant que colonne d'identité et les colonnes qui ont des données en double sont Column1 , Column2 et 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) 

Le script suivant montre l'utilisation de GROUP BY , HAVING , ORDER BY dans une requête et renvoie les résultats avec une colonne en double et son nombre.

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

Oh, bien sûr. Utilisez une table temporaire. Si vous voulez une déclaration unique, pas très performante qui "fonctionne" vous pouvez aller avec:

DELETE FROM MyTable WHERE NOT RowID IN
    (SELECT 
        (SELECT TOP 1 RowID FROM MyTable mt2 
        WHERE mt2.Col1 = mt.Col1 
        AND mt2.Col2 = mt.Col2 
        AND mt2.Col3 = mt.Col3) 
    FROM MyTable mt)

Fondamentalement, pour chaque ligne de la table, le sous-select trouve le RowID supérieur de toutes les lignes qui sont exactement comme la ligne considérée. Vous obtenez ainsi une liste des RowID représentant les lignes "originales" non dupliquées.


Une autre façon de le faire: -

DELETE A
FROM   TABLE A,
       TABLE B
WHERE  A.COL1 = B.COL1
       AND A.COL2 = B.COL2
       AND A.UNIQUEFIELD > B.UNIQUEFIELD 

Une autre façon possible de le faire est

; 

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

J'utilise ORDER BY (SELECT 0) ci-dessus car il est arbitraire quelle ligne conserver en cas d'égalité.

Pour conserver le dernier en ordre RowID par exemple, vous pouvez utiliser ORDER BY RowID DESC

Plans d'exécution

Le plan d'exécution pour cela est souvent plus simple et plus efficace que celui de la réponse acceptée car il ne nécessite pas l'auto-jointure.

Ce n'est pas toujours le cas cependant. Un endroit où la solution GROUP BY pourrait être préférée est des situations où un agrégat de hachage serait choisi de préférence à un agrégat de flux.

La solution ROW_NUMBER donnera toujours à peu près le même plan alors que la stratégie GROUP BY est plus flexible.

Les facteurs qui pourraient favoriser l'approche de l'agrégat de hachage seraient les suivants:

  • Aucun index utile sur les colonnes de partitionnement
  • relativement moins de groupes avec relativement plus de doublons dans chaque groupe

Dans les versions extrêmes de ce second cas (s'il y a très peu de groupes avec beaucoup de doublons) on pourrait aussi simplement insérer les lignes pour les conserver dans une nouvelle table puis TRUNCATE -signer l'original et les recopier pour minimiser la journalisation par rapport à la suppression une très forte proportion des rangées.


Utilisez ceci

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

Voici un autre bon article sur la suppression des doublons .

Il explique pourquoi il est difficile: " SQL est basé sur l'algèbre relationnelle, et les doublons ne peuvent pas se produire dans l'algèbre relationnelle, car les doublons ne sont pas autorisés dans un ensemble. "

La solution de la table temporaire et deux exemples mysql.

À l'avenir, allez-vous l'empêcher au niveau de la base de données ou du point de vue de l'application? Je suggérerais le niveau de base de données parce que votre base de données devrait être responsable de maintenir l'intégrité référentielle, les développeurs juste causeront des problèmes;)


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