DOUBLONS : détecter les doublons partiels

Comment détecter les lignes d'une table faisant doublon sur une liste de colonne (et non pas sur l'ensemble de la ligne).

Exemple :

SQL> CREATE TABLE toto (
  2     pays VARCHAR2(3),
  3     ville VARCHAR2(10),
  4     note NUMBER
  5  );

Table created.

SQL> INSERT INTO toto VALUES ('FRA','PARIS',1);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','PARIS',2);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','PARIS',3);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','PARIS',3);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','LYON',4);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','LYON',5);

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','MARSEILLE',5);

1 row created.

SQL> INSERT INTO toto VALUES ('USA','NEW YORK',5);

1 row created.

SQL> COMMIT;

Commit complete.

Pour le cas classique :

SQL> SELECT t1.pays, t1.ville, t1.note, count(*)
  2  FROM toto t1
  3  GROUP BY t1.pays, t1.ville, t1.note
  4  HAVING COUNT(*) > 1
  5  ;

PAY VILLE            NOTE   COUNT(*)
--- ---------- ---------- ----------
FRA PARIS               3          2

Et la liste :

SQL> SELECT t1.* FROM toto t1 WHERE t1.rowid >= (
  2     SELECT MIN(t2.rowid) FROM toto t2
  3     WHERE t1.pays=t2.pays
  4       AND t1.ville=t2.ville
  5       AND t1.note=t2.note
  6     HAVING COUNT(*) > 1
  7  )
  8  ;

PAY VILLE            NOTE
--- ---------- ----------
FRA PARIS               3

FRA PARIS               3

Et par extension, pour le cas des doublons partiels :

SQL> SELECT t1.pays, t1.ville, COUNT(*)
  2  FROM toto t1
  3  GROUP BY t1.pays, t1.ville
  4  HAVING COUNT(*) > 1
  5  ;

PAY VILLE        COUNT(*)
--- ---------- ----------
FRA LYON                2
FRA PARIS               4

Et la liste :

SQL> SELECT t1.* FROM toto t1 WHERE t1.rowid >= (
  2     SELECT MIN(t2.rowid) FROM toto t2
  3     WHERE t1.pays=t2.pays
  4       AND t1.ville=t2.ville
  5     HAVING COUNT(*) > 1
  6  )
  7  ;

PAY VILLE            NOTE
--- ---------- ----------
FRA PARIS               1
FRA PARIS               2
FRA PARIS               3
FRA PARIS               3
FRA LYON                4
FRA LYON                5

ROWID : Détecter puis supprimer les doublons d'une table

Problème classique : détection puis suppression de doublons.

SQL> CREATE TABLE toto(code_pays CHAR(3), ville VARCHAR2(30));

Table created.

SQL> INSERT INTO toto VALUES ('FRA','PARIS');

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','LYON');

1 row created.

SQL> INSERT INTO toto VALUES ('FRA','LYON');

1 row created.

SQL> INSERT INTO toto VALUES ('USA','DENVER');

1 row created.

SQL> SELECT code_pays, ville, COUNT(*) FROM toto GROUP BY code_pays, ville HAVING COUNT(*) > 1;

COD VILLE                            COUNT(*)
--- ------------------------------ ----------
FRA LYON                                    2

SQL> DELETE FROM toto a WHERE ROWID > (SELECT MIN(b.rowid) FROM toto b WHERE a.code_pays=b.code_pays AND a.ville=b.ville);

1 row deleted.

SQL> SELECT code_pays, ville, COUNT(*) FROM toto GROUP BY code_pays, ville HAVING COUNT(*) > 1;

no rows selected.

Cas où l'une des colonnes contient des valeurs nulles parmi les lignes en doublons

Exemple :

SQL> INSERT INTO toto VALUES (null,'PARIS');

1 row created.

SQL> INSERT INTO toto VALUES (null,'PARIS');

1 row created.

SQL> INSERT INTO toto VALUES (null,'PARIS');

1 row created.

SQL> SELECT code_pays, ville, COUNT(*) FROM toto GROUP BY code_pays, ville HAVING COUNT(*) > 1;

COD VILLE                            COUNT(*)
--- ------------------------------ ----------
    PARIS                                   3

SQL> DELETE FROM toto a WHERE ROWID > (
  2  SELECT MIN(b.rowid) FROM toto b
  3  WHERE a.code_pays=b.code_pays AND a.ville=b.ville
  4  );

0 rows deleted.

SQL> DELETE FROM toto a WHERE ROWID > (
  2  SELECT MIN(b.rowid) FROM toto b
  3  WHERE (a.code_pays=b.code_pays AND a.ville=b.ville)
  4  OR (
  5    a.code_pays IS NULL AND b.code_pays IS NULL
  6    AND a.ville=b.ville
  7  )
  8  );

2 rows deleted.