Afficher doublons qui contiennent une même partie de chaine de caractères [Résolu]

[xavyeii]

Bonsoir la communauté,

Je souhaite afficher les doublons qui possèdent une partie de chaine de caractères identique dans un champ d'une même table.

Par exemple, ma table 'table1' possède les champs: 'id', 'nom', 'cp'.

Code :

1
2
3
4
5
6
 
id                    nom                    cp
1            PARIS 01 - 75001              75001
2            PARIS 01 - 75111              75111
3            PARIS 02 - 75002              75002
...                   ...                   ...

Dans ce cas, je souhaite afficher les deux premières lignes car il existe deux 'PARIS 01'.


Je pense à quelque chose qui pourrait ressembler à ceci:

Code :

1
2
3
4
5
6
7
SELECT DISTINCT `nom` 
FROM `table1` T1 
WHERE EXISTS (
  SELECT `nom` 
  FROM `table1` T2 
  WHERE (UPPER(T1.`nom`) = UPPER(T2.`nom`))
)

J'ai effectué pas mal de recherche mais en vain...


Merci de l'aide.
Cordialement.

[CinePhil]

UPPER va mettre en lettres capitales le contenu de la colonne. Si tu veux trouver les deux 'PARIS 01', il ne faut pas travailler sur la totalité de la colonne nom mais seulement sur une partie. Si ça ne concerne que Paris et ses arrondissements, tu peux travailler sur les 8 premier caractères, LEFT(nom, 8), de la colonne. S'il y a d'autres villes mais que la colonne nom se termine toujours par le code postal qui en France est toujours de 5 caractères, il faut travailler sur la longueur de la colonne - 5 caractères LEFT(nom, LENGTH(nom) - 5).

[xavyeii]

Merci de votre réponse.

En effet, il n'y a pas que Paris mais la colonne 'nom' se termine toujours par les 5 chiffres du code postal.

J'arrive donc à afficher les caractères de la colonne 'nom' excepté le code postal grâce à:

Code :

LEFT(nom, LENGTH(nom) - 5)

Je n'arrive cependant pas à l'intégrer dans le code qui permet d'afficher que les doublons. J'ai essayé quelque chose du genre:

Code :

1
2
3
4
5
6
7
SELECT DISTINCT `nom`, `cp`
FROM `table1` T1 
WHERE EXISTS (
SELECT `nom` 
FROM `table1` T2 
WHERE LEFT(T1.`nom`, LENGTH(T1.`nom`) - 5) = LEFT(T2.`nom`, LENGTH(T2.`nom`) - 5)
)

Cela me retourne tout.

Merci
Cordialement.

[Madfrix]

Bonjour,

c'est typiquement ici un problème de modélisation, les données ne sont pas assez atomisées mais trop agrégées dans une colonne nom ce qui pose ensuite ce genre de problème.

Que donne la requête suivante ?

Code sql :

1
2
3
4
5
6
7
8
9
 
SELECT DISTINCT nom, cp
FROM table1 T1 
WHERE EXISTS (
   SELECT 1 
   FROM table1 T2 
   WHERE LEFT(T1.nom, LENGTH(T1.nom) - 5) = LEFT(T2.nom, LENGTH(T2.nom) - 5)
   AND T1.id <> T2.id
)

[xavyeii]

Bonsoir,

@Madfrix

Dans une petite table votre requête fonctionne correctement. Maintenant dans ma table avec environ 330 000 entrées, il cherche et cherche... Sans résultat.

Avez-vous une solution à cela?

Cordialement.

[Madfrix]

C'est l'enchainement des fonctions de chaines qui font ramer.

Autre alternative probablement plus rapide :

Code :

1
2
3
4
5
 
SELECT DISTINCT nom, cp
FROM table1 T1 
GROUP BY nom, cp, LEFT(T1.nom, LENGTH(T1.nom) - 5)
HAVING COUNT(*) > 1

et mettre un index sur nom

[xavyeii]

Arf! Ca ne retourne toujours rien.. Je ne trouve toujours pas comment je pourrai afficher les doublons de ma grande table.

Est-il possible de rechercher pas toute la table d'un coup? Partie par partie? DU genre 'id'=1 à id='500' ?

Merci.
Cordialement.

[CinePhil]

330 000 lignes, ce n'est pas une grande table !

Le problème est que les index ne peuvent pas être utilisés à cause de la fonction sur la chaîne de caractère.

Essaie avec une table temporaire :

Code :

1
2
3
4
5
6
7
8
CREATE TEMPORARY TABLE tmp
SELECT id, LEFT(nom, LENGTH(nom) - 5) AS debut_nom
FROM table1
ORDER BY id;
 
ALTER TABLE tmp
ADD PRIMARY KEY (id),
ADD INDEX (debut_nom);

Note : J'ai supposé que la colonne id de la table1 n'a que des valeurs uniques.

Une fois qu'on a cette table temporaire indexée, on peut travailler plus efficacement avec en étant débarrassé des fonctions de chaînes de caractères.

Quels sont les doublons dans la table temporaire ?

Code :

1
2
3
4
SELECT debut_nom
        FROM tmp
        GROUP BY debut_nom
        HAVING COUNT(*) > 1

On récupère tous les id correspondant à ces doublons :

Code :

1
2
3
4
5
6
7
8
9
SELECT t1.id, t1.debut_nom, t1.cp
FROM tmp t1
INNER JOIN 
(    
    SELECT debut_nom
    FROM tmp
    GROUP BY debut_nom
    HAVING COUNT(*) > 1
) temp ON temp.debut_nom = t1.debut_nom

Ce qui donne au final l'enchaînement de requêtes suivant :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TEMPORARY TABLE tmp
SELECT id, 
    LEFT(nom, LENGTH(nom) - 5) AS debut_nom,
    cp
FROM table1
ORDER BY id;
 
ALTER TABLE tmp
ADD PRIMARY KEY (id),
ADD INDEX (debut_nom);
 
SELECT id, debut_nom, cp
FROM tmp t1
INNER JOIN 
(    
    SELECT debut_nom
    FROM tmp
    GROUP BY debut_nom
    HAVING COUNT(*) > 1
) temp ON temp.debut_nom = t1.debut_nom;

[xavyeii]

La colonne 'id' n'a en effet que des valeurs uniques (Auto Incrémenté).

J'ai tester l'enchainement des requêtes. J'ai testé des variantes également. La table temporaire se créée correctement mais l'erreur suivante apparait: "#1137 - Can't reopen table: 't1'"


Merci de l'aide.
Cordialement.

[CinePhil]

Quand je dis "enchaînement de requêtes", ça veut dire qu'il fautr les lancer dans la même connexion.
Dans phpMyAdmin, tu lances le paquet de requêtes d'un coup.
En PHP, tu ouvre une connexion à la BDD, tu lance les requêtes l'une derrière l'autre puis tu fermes la connexion.

[xavyeii]

Je travaille directement dans phpMyAdmin et j'avais bien lancer ceci d'un coup:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TEMPORARY TABLE tmp
SELECT id, 
    LEFT(nom, LENGTH(nom) - 5) AS debut_nom,
    cp
FROM table1
ORDER BY id;
 
ALTER TABLE tmp
ADD PRIMARY KEY (id),
ADD INDEX (debut_nom);
 
SELECT id, debut_nom, cp
FROM tmp t1
INNER JOIN 
(    
    SELECT debut_nom
    FROM tmp
    GROUP BY debut_nom
    HAVING COUNT(*) > 1
) temp ON temp.debut_nom = t1.debut_nom;

Merci

[CinePhil]

Un coup de avec le message d'erreur m'a conduit sur un message qui explique qu'on ne peut utiliser deux fois une table temporaire dans la même requête.
Crée une vraie table puis supprime là quand tu n'en as plus besoin :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE tmp
SELECT id, 
    LEFT(nom, LENGTH(nom) - 5) AS debut_nom,
    cp
FROM table1
ORDER BY id;
 
ALTER TABLE tmp
ADD PRIMARY KEY (id),
ADD INDEX (debut_nom);
 
SELECT id, debut_nom, cp
FROM tmp t1
INNER JOIN 
(    
    SELECT debut_nom
    FROM tmp
    GROUP BY debut_nom
    HAVING COUNT(*) > 1
) temp ON temp.debut_nom = t1.debut_nom;

[xavyeii]

En effet. Bien vu CinePhil.

Ceci étant il continuait de m'afficher une erreur: "#1052: Column 'debut_nom' in field list is ambiguous"

Après une nouvelle recherche, j'ai trouvé la réponse, ce qui donne au final:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE tmp
SELECT id, 
    LEFT(nom, LENGTH(nom) - 5) AS debut_nom,
    cp
FROM table1
ORDER BY id;
 
ALTER TABLE tmp
ADD PRIMARY KEY (id),
ADD INDEX (debut_nom);
 
SELECT id, t1.debut_nom, cp
FROM tmp t1
INNER JOIN 
(    
    SELECT debut_nom
    FROM tmp
    GROUP BY debut_nom
    HAVING COUNT(*) > 1
) temp ON temp.debut_nom = t1.debut_nom;

Et ça fonctionne!
Merci beaucoup.