Besoin d'aide pour une requête de recherche d'anagramme dans une table

**Boazee** · 08/03/2017, 19h18

Bonjour à tous et toutes et merci pour tout le travail accompli sur ce site.

J'ai besoin d'aide pour une requête dont je ne sais à vrai dire pas si elle est réalisable.

J'ai une base de données de lexique avec des codes de 3 lettres (plus de 1000 lignes)
chaque ligne contient :

- un ID, chiffré et auto-incrémenté
- le code à 3 lettres (unique, qui aurait pu servir de clé si la table avait été bien conçu)
- un court descriptif lié au code

je voudrais savoir s'il est possible selon vous de faire une requête (probablement via un système de jointure interne ??) qui permettrait de vérifier pour chaque entrée si son anagramme existe dans la table et d'afficher le ou les résultats en vis à vis, sachant qu'il y aura au maximum 5 possibilités si mes calculs sont bons :

exemple pour un code ABC le but serait de chercher si les codes ACB, BAC, BCA, CAB et CBA existent et s'il existent de les afficher sur la même ligne ainsi que leur descriptif

si ce n'est pas assez clair voici un schéma simplifié de la table sur 2 lignes

ID | code |Descriptif
0001 | ABC |Descrittif d'ABC
0002 | DEF |Descrittif de DEF
etc...etc...

et je voudrais obtenir si possible la table suivante :

ABC | Descriptif d'ABC | ACB | Descriptif d'ACB | BAC | Descriptif de BAC | BCA | Descriptif de BCA | CAB | Descriptif de CAB | CBA | Descriptif de CBA
DEF | Descriptif d'ABC | DFE | Descriptif de DFE | EDF | Descriptif de EDF | EFD | Descriptif de EFD | FDE | Descriptif de FDE | FED | Descriptif de FED
etc...etc...

et lorsque qu'un code et son descriptif n'existent pas dans la table écrire NULL ou Néant à la place.... du genre :

GHI | Descriptif de GHI | GIH | Descriptif de GIH | NULL | NULL | HIG | Descriptif de HIG | NULL | NULL | NULL | NULL

Pensez-vous qu'une telle chose soit envisageable ou alors qu'il vaut mieux laisser tomber et faire cela via une page web en php avec une boucle? (ce que je souhaiterai éviter si possible bien que sachant comment faire cela en PHP)

Sinon peut-être sans allez aussi loin que cela, auriez vous une piste ou idée pour rechercher les anagrammes des codes au sein d'une même table?

Merci par avance.

**vttman** · 09/03/2017, 09h59

Une piste oui

mais est-ce la bonne ?
Prenons un exemple

table_ana

chpa
----------
ABC
BAC
CBA
KAl
IAK

Ici une requête pour identifier les anagrammes de type XYZ et ZYX uniquement ...
Un point de départ ou réflexion, peut-être ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
select 
t.chpa
from table_ana t,table_ana t1  where 
SUBSTRING(t.chpa,1,1) = SUBSTRING(t1.chpa,3,1) 
and SUBSTRING(t.chpa,2,1) = SUBSTRING(t1.chpa,2,1) 
and SUBSTRING(t.chpa,3,1) = SUBSTRING(t1.chpa,1,1)
and t.chpa < t1.chpa
group by 1

Note 1 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Le t.chpa < t1.chpa

permet de n'afficher que XYZ quand il y a XYZ et ZYX ...

=>

chpa / chpa
----------
ABC / CBA
IAK / KAI

Note 2 : Attention BAC n'apparait pas car cette configuration n'a pas (encore) été prise en compte

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
select 
chpa from table_ana
where chpa not in 
(
select 
t.chpa
from table_ana t,table_ana t1  where 
SUBSTRING(t.chpa,1,1) = SUBSTRING(t1.chpa,3,1) 
and SUBSTRING(t.chpa,2,1) = SUBSTRING(t1.chpa,2,1) 
and SUBSTRING(t.chpa,3,1) = SUBSTRING(t1.chpa,1,1)
)

=>

chpa
----------
BAC

Bon ça fait une réponse ...

**Boazee** · 09/03/2017, 12h05

Salut et merci à toi vttman

Effectivement cela ne répond pas totalement à ma problématique mais ta piste est super intéressante d'autant plus que je découvre des principe dans ton code que je ne connaissait absolument pas...

toutefois une petite correction de ton code au passage :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
select 
t.chpa,t1.chpa
from table_ana t,table_ana t1  where 
SUBSTRING(t.chpa,1,1) = SUBSTRING(t1.chpa,3,1) 
and SUBSTRING(t.chpa,2,1) = SUBSTRING(t1.chpa,2,1) 
and SUBSTRING(t.chpa,3,1) = SUBSTRING(t1.chpa,1,1)
and t.chpa < t1.chpa
group by 1

en effet pour afficher les deux colonnes en vis à vis il faut :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
select 
t.chpa,t1.chpa

Du moins c'est ce que j'ai constaté en l'essayant sur ma table.

Pour le reste il va me falloir encore un peu de temps pour digérer et analyser les script que tu viens de me fournir...

Petit question d'où vient ce principe de "from table_ana t,table_ana t1" que je ne connaissais pas du tout, à moins que j'ai déja vu ça dans des cours par le passé mais que ça m'ai échappé . Un lien vers des infos complémentaire seraient apprécié

Encore merci pour cette piste qui me sera très utile pour optimiser un code php si toutefois la solution complète n'existe pas en SQL

**vttman** · 09/03/2017, 12h13

Oui j'ai oublié d'afficher un champ ... je vais me fouetter de suite

Pour cette partie là

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
select 
t.chpa,t1.chpa
from table_ana t,table_ana t1  where 
SUBSTRING(t.chpa,1,1) = SUBSTRING(t1.chpa,3,1) 
and SUBSTRING(t.chpa,2,1) = SUBSTRING(t1.chpa,2,1) 
and SUBSTRING(t.chpa,3,1) = SUBSTRING(t1.chpa,1,1)
and t.chpa < t1.chpa
group by 1

J'aurais pu/du écrire

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
select 
t.chpa,t1.chpa
from table_ana t inner join table_ana t1  
on SUBSTRING(t.chpa,1,1) = SUBSTRING(t1.chpa,3,1) 
and SUBSTRING(t.chpa,2,1) = SUBSTRING(t1.chpa,2,1) 
and SUBSTRING(t.chpa,3,1) = SUBSTRING(t1.chpa,1,1)
where t.chpa < t1.chpa
group by 1

Je ne l'ai pas fait car le inner join ... on ...
=> l'utilisation des identifiants pour réaliser mes jointures mais dans notre cas ce sont
des parties d'identifiant alors bon !?

**Boazee** · 09/03/2017, 12h20

Je te réitère mes remerciements vttman

à peine 10 minutes que je suis sur ton script et il m'a déja ouvert des horizons insoupçonnés et donné plusieurs idées... même s'il ne résoud pas ma problématique initiale, je vais beaucoup m'amuser avec ce que tu viens de me faire découvrir...

**Artemus24** · 11/03/2017, 08h48

Salut Boazee.

Pour résoudre votre problème, vous devez ajouter une nouvelle colonne, de nom "lien".
Dans cette colonne, vous mettez la valeur de la colonne "id", qui va pointer sur la ligne qui va servir de référence à votre anagramme.
Il vous sera plus facile d'extraire, par un group_concat(), toutes les lignes ayant la même anagramme.

Par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`          integer unsigned  not null auto_increment primary key,
  `code`        char(03)          not null,
  `descriptif`  varchar(255)      not null,
  `lien`        integer unsigned  not null
 ) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into   `test` (`code`,`descriptif`,`lien`) values
  ('bac', 'Descriptif de BAC',  1),
  ('cas', 'Descriptif de CAS',  2),
  ('abc', 'Descriptif de ABC',  1),
  ('cul', 'Descriptif de CUL',  4),
  ('cab', 'Descriptif de CAB',  1),
  ('luc', 'Descriptif de LUC',  4),
  ('cba', 'Descriptif de CBA',  1),
  ('sac', 'Descriptif de SAC',  2),
  ('acb', 'Descriptif de ACB',  1),
  ('bol', 'Descriptif de BOL', 10),
  ('bca', 'Descriptif de BCA',  1),
  ('lob', 'Descriptif de LOB', 10)
--------------
 
--------------
select * from `test`
--------------
 
+----+------+-------------------+------+
| id | code | descriptif        | lien |
+----+------+-------------------+------+
|  1 | bac  | Descriptif de BAC |    1 |
|  2 | cas  | Descriptif de CAS |    2 |
|  3 | abc  | Descriptif de ABC |    1 |
|  4 | cul  | Descriptif de CUL |    4 |
|  5 | cab  | Descriptif de CAB |    1 |
|  6 | luc  | Descriptif de LUC |    4 |
|  7 | cba  | Descriptif de CBA |    1 |
|  8 | sac  | Descriptif de SAC |    2 |
|  9 | acb  | Descriptif de ACB |    1 |
| 10 | bol  | Descriptif de BOL |   10 |
| 11 | bca  | Descriptif de BCA |    1 |
| 12 | lob  | Descriptif de LOB |   10 |
+----+------+-------------------+------+
--------------
select  group_concat(descriptif order by descriptif separator ' - ') as descriptif
           from  test as t1
       group by  lien
--------------
 
+-----------------------------------------------------------------------------------------------------------------------+
| descriptif                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| Descriptif de ABC - Descriptif de ACB - Descriptif de BAC - Descriptif de BCA - Descriptif de CAB - Descriptif de CBA |
| Descriptif de CAS - Descriptif de SAC                                                                                 |
| Descriptif de CUL - Descriptif de LUC                                                                                 |
| Descriptif de BOL - Descriptif de LOB                                                                                 |
+-----------------------------------------------------------------------------------------------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

Toute la difficulté repose sur comment remplir cette colonne "lien".
Cela doit se faire à chaque nouvelle insertion dans votre table.
Vous devez rechercher dans la colonne "id", la plus petite valeur ayant l'anagramme de ce que vous allez insérer.
S'il n'existe aucun anagramme, alors dans "lien", vous mettez la même valeur que celle de la colonne "id".

Par contre, pour tester les anagrammes, vous devez le faire, non pas dans MySql, mais dans php.
Voici un exemple de toutes les solutions d'un anagramme sur quatre lettres.
Ce programme a été écrit en Perl :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
#!C:\perl64\bin\perl.exe -w
 
use strict;
use warnings;
use diagnostics;
 
# ---------
# Anagramme
# ---------
 
sub swap {
	my ($x,$y,$car) = @_;
	my $t     = @$car[$x];
	@$car[$x] = @$car[$y];
	@$car[$y] = $t;
return @_;
}
 
sub anagram {
	my ($i, $l, $val) = @_;
 
	if ($i == $l)	{ print "@$val\n"; }
	else			{ for (my $j=$i; $j<=$l; $j++) {
							&swap($i,$j,$val) unless ($i == $j);
							&anagram($i+1,$l,$val);
							&swap($i,$j,$val) unless ($i == $j); }
						}
return @_;
}
 
# -------
# Exemple
# -------
 
my @car = ("a","b","c","d");
 
&anagram(0, 3, \@car);

Et voici le résultat de ce programme :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
a b c d
a b d c
a c b d
a c d b
a d c b
a d b c
b a c d
b a d c
b c a d
b c d a
b d c a
b d a c
c b a d
c b d a
c a b d
c a d b
c d a b
c d b a
d b c a
d b a c
d c b a
d c a b
d a c b
d a b c
 
Appuyez sur une touche pour continuer...

A vous de l'adapter à ce que vous cherchez à faire.

@+

**Boazee** · 14/03/2017, 09h39

Envoyé par Artemus24

Cela doit se faire à chaque nouvelle insertion dans votre table.
Vous devez rechercher dans la colonne "id", la plus petite valeur ayant l'anagramme de ce que vous allez insérer.
S'il n'existe aucun anagramme, alors dans "lien", vous mettez la même valeur que celle de la colonne "id".

Merci Artemus24

mais si je comprends bien votre méthode, étant donné que dans mon cas la table existe déjà et comporte pas loin de 1000 lignes, il faut que je trouve un moyen d'inscrire un nombre "clé" dans une colonne "lien" (de manière virtuelle, ou bien physique via un insert) en me basant sur l'ID de la première occurence d'anagramme rencontrée... ça semble compliqué mais faisable... et ensuite faire le reste en PHP (je ne connais pas PERL, mais il faudrait peut-être que je m'y intéresse???... y'a t-il des avantage par rapport à PHP? Ou est-ce juste une histoire préférence...)

ça ne résout pas le problème non plus mais c'est une solution.... Mais en PHP avec un système de boucle je peux aisément faire ce que je voulais, je cherchai surtout un moyen de traité cela en SQL... peut-être via une fonction ou un truc du genre.

D'ailleurs au passage, j'ai résolut temporairement mon problème en créant une nouvelle table grâce au script proposé par vttman... Pour ce faire j'ai produit 5 tables intermédiaire que j'ai exporté en CSV et ensuite j'ai fusionné ces 5 tables en une seule (via openoffice calc) puis réimporté en une table "Anagrammes" dans Mysql...

mais le souci c'est que si je rentre une nouvelle données dans ma table de base il faudra à chaque fois que je vérifie si il existe des anagrammes de cette nouvelle entrée et que je mette la table d'anagramme à jour manuellement...

Je sais pas si je dois mettre le sujet comme résolu alors qu'en fait il ne l'est pas .... bien qu'ayant solutionné de mon côté mon problème dans l'urgence, de manière certes un peu barbare, via la méthode à vttman.

Je ne m'y connais vraiment pas en "création" de fonctions SQL il faudrait surement que je m'y mette et que je fouille de ce coté là car si solution il y a je pense que ça passe forcément par la mise en place d'une fonction...

Quoi qu'il en soit merci pour ton aide Artemus24.

@ Artemus24 : Joli l'avatar d'Artemus Gordon, les mystères de l'ouest... Même si ça rappelle des souvenirs qui ne nous rajeunissent pas...

**Artemus24** · 15/03/2017, 05h45

Salut Boazee.

1000 lignes, ce n'est pas grand chose comme volumétrie !
Sinon, vous avez bien compris ce qu'il faut faire.

Envoyé par Boazee

ça semble compliqué mais faisable...

Mais parce que vous prenez le problème à l'envers.
Vous vous posez la question de la faisabilité quand votre modélisation est terminée.
Alors que cette question aurait dû être posée dès le départ.

1) associer les différentes combinaisons de vos anagrammes en les liant par une colonne "lien".
Quand le lien est fait, rechercher tous les anagrammes devient un jeu d'enfant. Une requête qui s'écrit sur trois lignes !

2) au préalable, lors de l'insertion, vous devez rechercher le plus petit anagramme qui a été déjà inséré dans votre table, s'il existe.
Si c'est le cas, dans la nouvelle insertion, vous mettez cet identifiant.
Dans le cas contraire, vous pointez cette nouvelle insertion sur elle-même.

Pourquoi procéder ainsi ?
Il vaut mieux perdre un peu de temps lors de l'insertion et en gagner lors de la rechercher de l'anagramme dans votre table.
C'est juste une question de bon sens.

Envoyé par Boazee

je ne connais pas PERL, mais il faudrait peut-être que je m'y intéresse???... y'a t-il des avantage par rapport à PHP? Ou est-ce juste une histoire préférence...

C'est juste un exemple que je vous donne. J'aurai très bien pu vous le faire en 'C' ou en php. Juste une histoire de préférence, comme vous dites.

Envoyé par Boazee

Mais en PHP avec un système de boucle je peux aisément faire ce que je voulais,

Ca, c'est la méthode bourin.

Envoyé par Boazee

je cherchais surtout un moyen de traité cela en SQL... peut-être via une fonction ou un truc du genre.

Ce que je vous ai donné, c'est la solution la plus optimale, mais pas nécessairement la plus facile à mettre en oeuvre.

Sinon, par l'approche de vttman, vous devez donner toutes les combinaisons possibles d'un anagramme de trois lettres. Ce qui donne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--------------
select distinct  group_concat(t2.descriptif order by t2.code separator ' - ') as descriptif
           from  test as t1
left outer join  (        select  concat(substring(code,1,1), substring(code,2,1), substring(code,3,1)) as ref, code, descriptif from test
                    union select  concat(substring(code,1,1), substring(code,3,1), substring(code,2,1)) as ref, code, descriptif from test
                    union select  concat(substring(code,2,1), substring(code,1,1), substring(code,3,1)) as ref, code, descriptif from test
                    union select  concat(substring(code,2,1), substring(code,3,1), substring(code,1,1)) as ref, code, descriptif from test
                    union select  concat(substring(code,3,1), substring(code,1,1), substring(code,2,1)) as ref, code, descriptif from test
                    union select  concat(substring(code,3,1), substring(code,2,1), substring(code,1,1)) as ref, code, descriptif from test
                 ) as t2
             on  t2.ref = t1.code
       group by  t2.ref
--------------
 
+-----------------------------------------------------------------------------------------------------------------------+
| descriptif                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| Descriptif de ABC - Descriptif de ACB - Descriptif de BAC - Descriptif de BCA - Descriptif de CAB - Descriptif de CBA |
| Descriptif de BOL - Descriptif de LOB                                                                                 |
| Descriptif de CAS - Descriptif de SAC                                                                                 |
| Descriptif de CUL - Descriptif de LUC                                                                                 |
+-----------------------------------------------------------------------------------------------------------------------+
--------------

Envoyé par Boazee

il faudra à chaque fois que je vérifie si il existe des anagrammes de cette nouvelle entrée et que je mette la table d'anagramme à jour manuellement...

Pourquoi le faire manuellement ? Si je vous ai donné le programme perl, ce n'est pas pour faire joli dans ce sujet !
C'est ce que vous devez programmer en php pour obtenir les différents anagrammes d'un même code de trois lettres.
Et si vous passez à quatre, cinq lettres, ou plus, c'est le même algorithme récursif.
Il faut juste changer la largeur de de votre anagramme et c'est tout.

@+

**Boazee** · 15/03/2017, 09h51

Salut Artemus24

merci pour ces précisions.

Je vais explorer cette piste et si toutefois j'arrive à élaborer une requête uniquement en SQL sans recours à PHP ou autre, alors je reviendrais poster la solluce.

Envoyé par Artemus24

Salut Boazee.
1) associer les différentes combinaisons de vos anagrammes en les liant par une colonne "lien".
Quand le lien est fait, rechercher tous les anagrammes devient un jeu d'enfant. Une requête qui s'écrit sur trois lignes !

Je reformule donc ma question initiale à partir de cette position... supposons que j'insère une colonne "lien" exactement comme vous le dite avec un code unique par groupe d'anagramme... ce qui sera assez aisé à faire.

Comment à partir de là verriez vous le script SQL (uniquement SQL sans PHP ou autre) pour afficher générer une table de ce genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
ABC | Descriptif d'ABC | ACB | Descriptif d'ACB | BAC | Descriptif de BAC | BCA | Descriptif de BCA | CAB | Descriptif de CAB | CBA | Descriptif de CBA 
DEF | Descriptif d'ABC | DFE | Descriptif de DFE | EDF | Descriptif de EDF | EFD | Descriptif de EFD | FDE | Descriptif de FDE | FED | Descriptif de FED 
etc...etc...

Est-ce possible uniquement avec MySQL ?

Je n'ai pas l'impression que ce soit possible, sans passer par un système de boucle ou en créant une fonction, mais peut-être que je me trompe.

Il se peut que l'on soit très près de la soluce en fait via ce code très intéressant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
--------------
select distinct  group_concat(t2.descriptif order by t2.code separator ' - ') as descriptif
           from  test as t1
left outer join  (        select  concat(substring(code,1,1), substring(code,2,1), substring(code,3,1)) as ref, code, descriptif from test
                    union select  concat(substring(code,1,1), substring(code,3,1), substring(code,2,1)) as ref, code, descriptif from test
                    union select  concat(substring(code,2,1), substring(code,1,1), substring(code,3,1)) as ref, code, descriptif from test
                    union select  concat(substring(code,2,1), substring(code,3,1), substring(code,1,1)) as ref, code, descriptif from test
                    union select  concat(substring(code,3,1), substring(code,1,1), substring(code,2,1)) as ref, code, descriptif from test
                    union select  concat(substring(code,3,1), substring(code,2,1), substring(code,1,1)) as ref, code, descriptif from test
                 ) as t2
             on  t2.ref = t1.code
       group by  t2.ref
--------------
 
+-----------------------------------------------------------------------------------------------------------------------+
| descriptif                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| Descriptif de ABC - Descriptif de ACB - Descriptif de BAC - Descriptif de BCA - Descriptif de CAB - Descriptif de CBA |
| Descriptif de BOL - Descriptif de LOB                                                                                 |
| Descriptif de CAS - Descriptif de SAC                                                                                 |
| Descriptif de CUL - Descriptif de LUC                                                                                 |
+-----------------------------------------------------------------------------------------------------------------------+
--------------

Que j'ai essayé sur ma table et qui fonctionne très bien... j'avais l'impression que ce serait lourd, mais pas du tout ... la requête a été traitée en 1.5172 sec"

Sauf que pour ma part je souhaiterai que "Descriptif de ABC" et "Descriptif de ACB" soient dans des colonnes séparés au niveau de la table obtenue et non pas concaténé dans une même colonne.

mais c'est une piste intéressante... j'ai l'impression qu'on tiens quelque chose là, mais c'est pas gagné...

**Boazee** · 15/03/2017, 15h55

Bon j'ai réussi à résoudre mon problème et à obtenir exactement ce que je voulais via un script sans fonction ni même jointure...
C'est d'ailleurs tellement simple que je ne comprends pas pourquoi je n'y ai pas pensé avant... probablement parceque je m'étais mis d'emblé dans la tête qu'il faudrait passé par une fonction ou un système de boucle...

voici le code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT `ID`,
`code` as code1,
concat(substring(`code`,1,1), substring(`code`,3,1), substring(`code`,2,1)) as code2 ,
concat(substring(`code`,2,1), substring(`code`,1,1), substring(`code`,3,1)) as code3 ,
concat(substring(`code`,2,1), substring(`code`,3,1), substring(`code`,1,1)) as code4 ,
concat(substring(`code`,3,1), substring(`code`,2,1), substring(`code`,1,1)) as code5 ,
concat(substring(`code`,3,1), substring(`code`,1,1), substring(`code`,2,1)) as code6,
(select `code` from tabletest where `code`=code1 ) as mot1,
(select `expr` from tabletest where `code`=code1 ) as descro1,
(select `code` from tabletest where `code`=code2 ) as mot2,
(select `expr` from tabletest where `code`=code2 ) as descro2,
(select `code` from tabletest where `code`=code3 ) as mot3,
(select `expr` from tabletest where `code`=code3 ) as descro3,
(select `code` from tabletest where `code`=code4 ) as mot4,
(select `expr` from tabletest where `code`=code4 ) as descro4,
(select `code` from tabletest where `code`=code5 ) as mot5,
(select `expr` from tabletest where `code`=code5 ) as descro5,
(select `code` from tabletest where `code`=code6 ) as mot6,
(select `expr` from tabletest where `code`=code6 ) as descro6
FROM tabletest

réponse du server : Traitement en 1.6513 sec pour un total de 1641 lignes
ce qui est plus que raisonnable ...

Le seul souci c'est qu'avec ce script je me retrouve avec 6 colonnes en trop qui ne me servent en réalité qu'a déclarer mes anagrammes, ce qui correspond à cette partie du script :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
`code` as code1,
concat(substring(`code`,1,1), substring(`code`,3,1), substring(`code`,2,1)) as code2 ,
concat(substring(`code`,2,1), substring(`code`,1,1), substring(`code`,3,1)) as code3 ,
concat(substring(`code`,2,1), substring(`code`,3,1), substring(`code`,1,1)) as code4 ,
concat(substring(`code`,3,1), substring(`code`,2,1), substring(`code`,1,1)) as code5 ,
concat(substring(`code`,3,1), substring(`code`,1,1), substring(`code`,2,1)) as code6,

on doit pouvoir déclarer les anagrammes autrement en SQL je suppose...

en attendant j'ai tenté une sous-requête ainsi :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
SELECT `ID`, mot1, descro1, mot2, descro2, mot3, descro3, mot4, descro4, mot5, descro5, mot6, descro6
FROM
(SELECT `ID`,
`code` as code1,
concat(substring(`code`,1,1), substring(`code`,3,1), substring(`code`,2,1)) as code2 ,
concat(substring(`code`,2,1), substring(`code`,1,1), substring(`code`,3,1)) as code3 ,
concat(substring(`code`,2,1), substring(`code`,3,1), substring(`code`,1,1)) as code4 ,
concat(substring(`code`,3,1), substring(`code`,2,1), substring(`code`,1,1)) as code5 ,
concat(substring(`code`,3,1), substring(`code`,1,1), substring(`code`,2,1)) as code6,
(select `code` from tabletest where `code`=code1 ) as mot1,
(select `expr` from tabletest where `code`=code1 ) as descro1,
(select `code` from tabletest where `code`=code2 ) as mot2,
(select `expr` from tabletest where `code`=code2 ) as descro2,
(select `code` from tabletest where `code`=code3 ) as mot3,
(select `expr` from tabletest where `code`=code3 ) as descro3,
(select `code` from tabletest where `code`=code4 ) as mot4,
(select `expr` from tabletest where `code`=code4 ) as descro4,
(select `code` from tabletest where `code`=code5 ) as mot5,
(select `expr` from tabletest where `code`=code5 ) as descro5,
(select `code` from tabletest where `code`=code6 ) as mot6,
(select `expr` from tabletest where `code`=code6 ) as descro6
FROM tabletest ) as tabletest2

ce qui a au moins l’intérêt de fonctionner et d'obtenir stricto ce que je souhaitais...

toutefois le souci c'est que ce coup-ci la requête s’exécutent en 87.2446 sec , toujours pour total de 1641 lignes.

On doit donc pouvoir optimiser cela... je continue à chercher.

**SQLpro** · 15/03/2017, 18h18

inspirez vous :
https://blog.developpez.com/sqlpro/p...ements_mathema

A +

**Boazee** · 15/03/2017, 23h45

Envoyé par SQLpro

inspirez vous :
https://blog.developpez.com/sqlpro/p...ements_mathema

A +

Merci pour le lien. Un peu compliqué pour moi qui suis amateur .

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
`code` as code1,
concat(substring(`code`,1,1), substring(`code`,3,1), substring(`code`,2,1)) as code2 ,
concat(substring(`code`,2,1), substring(`code`,1,1), substring(`code`,3,1)) as code3 ,
concat(substring(`code`,2,1), substring(`code`,3,1), substring(`code`,1,1)) as code4 ,
concat(substring(`code`,3,1), substring(`code`,2,1), substring(`code`,1,1)) as code5 ,
concat(substring(`code`,3,1), substring(`code`,1,1), substring(`code`,2,1)) as code6

ça plus de 3 heures que j’essaie d'alléger le script en déclarant les combinaisons de "code1" à "code6" de la partie de script ci dessus sans qu'elles ne s'affichent lors de l’exécution de la requête et pour éviter de passer par la sous-requête postée en fin du précédent message qui est trop longue à s'exécuter à mon gout...

Mais en vain!

J'ai essayer en créant une procédure mais je n'y arrive pas... j'ai même essayé de créer une fonction mais là c'est la cata...

Je vais donc me contenter de ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT `ID`,
`code` as code1,
concat(substring(`code`,1,1), substring(`code`,3,1), substring(`code`,2,1)) as code2 ,
concat(substring(`code`,2,1), substring(`code`,1,1), substring(`code`,3,1)) as code3 ,
concat(substring(`code`,2,1), substring(`code`,3,1), substring(`code`,1,1)) as code4 ,
concat(substring(`code`,3,1), substring(`code`,2,1), substring(`code`,1,1)) as code5 ,
concat(substring(`code`,3,1), substring(`code`,1,1), substring(`code`,2,1)) as code6,
(select `code` from tabletest where `code`=code1 ) as mot1,
(select `expr` from tabletest where `code`=code1 ) as descro1,
(select `code` from tabletest where `code`=code2 ) as mot2,
(select `expr` from tabletest where `code`=code2 ) as descro2,
(select `code` from tabletest where `code`=code3 ) as mot3,
(select `expr` from tabletest where `code`=code3 ) as descro3,
(select `code` from tabletest where `code`=code4 ) as mot4,
(select `expr` from tabletest where `code`=code4 ) as descro4,
(select `code` from tabletest where `code`=code5 ) as mot5,
(select `expr` from tabletest where `code`=code5 ) as descro5,
(select `code` from tabletest where `code`=code6 ) as mot6,
(select `expr` from tabletest where `code`=code6 ) as descro6
FROM tabletest

quitte à avoir 6 colonnes en plus c'est pas bien grave.... l'essentiel et que j'ai ce qu'il me faut.

je considère donc le problème comme étant résolu.

Merci à vous tous!

++

**Artemus24** · 16/03/2017, 02h55

Salut boazee.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`     integer unsigned  not null auto_increment primary key,
  `code`   char(03)          not null,
  `mess`   varchar(255)      not null,
  `lien`   integer unsigned      null default null
 ) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TRIGGER IF EXISTS `anagramme`
--------------
 
--------------
CREATE TRIGGER `anagramme`
BEFORE INSERT ON `test`
FOR EACH ROW
BEGIN
  declare _lien integer unsigned;
  set _lien = coalesce((select max(id)+1 from test), 1);
  select id from test where substring(code,1,1) = substring(new.code,1,1) and substring(code,2,1) = substring(new.code,3,1) and substring(code,3,1) = substring(new.code,2,1) and id < _lien into _lien;
  select id from test where substring(code,1,1) = substring(new.code,2,1) and substring(code,2,1) = substring(new.code,1,1) and substring(code,3,1) = substring(new.code,3,1) and id < _lien into _lien;
  select id from test where substring(code,1,1) = substring(new.code,2,1) and substring(code,2,1) = substring(new.code,3,1) and substring(code,3,1) = substring(new.code,1,1) and id < _lien into _lien;
  select id from test where substring(code,1,1) = substring(new.code,3,1) and substring(code,2,1) = substring(new.code,1,1) and substring(code,3,1) = substring(new.code,2,1) and id < _lien into _lien;
  select id from test where substring(code,1,1) = substring(new.code,3,1) and substring(code,2,1) = substring(new.code,2,1) and substring(code,3,1) = substring(new.code,1,1) and id < _lien into _lien;
  set new.lien = _lien;
END
--------------
 
--------------
insert into `test` (`code`,`mess`) values
  ('bac', 'Descriptif de BAC'),
  ('cas', 'Descriptif de CAS'),
  ('abc', 'Descriptif de ABC'),
  ('cul', 'Descriptif de CUL'),
  ('blo', 'Descriptif de BLO'),
  ('cab', 'Descriptif de CAB'),
  ('luc', 'Descriptif de LUC'),
  ('cba', 'Descriptif de CBA'),
  ('sac', 'Descriptif de SAC'),
  ('acb', 'Descriptif de ACB'),
  ('bol', 'Descriptif de BOL'),
  ('bca', 'Descriptif de BCA'),
  ('lob', 'Descriptif de LOB')
--------------
 
--------------
select * from `test`
--------------
 
+----+------+-------------------+------+
| id | code | mess              | lien |
+----+------+-------------------+------+
|  1 | bac  | Descriptif de BAC |    1 |
|  2 | cas  | Descriptif de CAS |    2 |
|  3 | abc  | Descriptif de ABC |    1 |
|  4 | cul  | Descriptif de CUL |    4 |
|  5 | blo  | Descriptif de BLO |    5 |
|  6 | cab  | Descriptif de CAB |    1 |
|  7 | luc  | Descriptif de LUC |    4 |
|  8 | cba  | Descriptif de CBA |    1 |
|  9 | sac  | Descriptif de SAC |    2 |
| 10 | acb  | Descriptif de ACB |    1 |
| 11 | bol  | Descriptif de BOL |    5 |
| 12 | bca  | Descriptif de BCA |    1 |
| 13 | lob  | Descriptif de LOB |    5 |
+----+------+-------------------+------+
--------------
select  group_concat(mess order by code separator ' - ') as mess
    from  test as t1
group by  lien
--------------
 
+-----------------------------------------------------------------------------------------------------------------------+
| mess                                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------+
| Descriptif de ABC - Descriptif de ACB - Descriptif de BAC - Descriptif de BCA - Descriptif de CAB - Descriptif de CBA |
| Descriptif de CAS - Descriptif de SAC                                                                                 |
| Descriptif de CUL - Descriptif de LUC                                                                                 |
| Descriptif de BLO - Descriptif de BOL - Descriptif de LOB                                                             |
+-----------------------------------------------------------------------------------------------------------------------+
--------------
select distinct  group_concat(t2.mess order by t2.code separator ' - ') as mess
           from  test as t1
left outer join  (  select  concat(substring(code,1,1),substring(code,2,1),substring(code,3,1)) as ref, code, mess from test union
                    select  concat(substring(code,1,1),substring(code,3,1),substring(code,2,1)) as ref, code, mess from test union
                    select  concat(substring(code,2,1),substring(code,1,1),substring(code,3,1)) as ref, code, mess from test union
                    select  concat(substring(code,2,1),substring(code,3,1),substring(code,1,1)) as ref, code, mess from test union
                    select  concat(substring(code,3,1),substring(code,1,1),substring(code,2,1)) as ref, code, mess from test union
                    select  concat(substring(code,3,1),substring(code,2,1),substring(code,1,1)) as ref, code, mess from test
                 ) as t2
             on  t2.ref = t1.code
       group by  t2.ref
--------------
 
+-----------------------------------------------------------------------------------------------------------------------+
| mess                                                                                                                  |
+-----------------------------------------------------------------------------------------------------------------------+
| Descriptif de ABC - Descriptif de ACB - Descriptif de BAC - Descriptif de BCA - Descriptif de CAB - Descriptif de CBA |
| Descriptif de BLO - Descriptif de BOL - Descriptif de LOB                                                             |
| Descriptif de CAS - Descriptif de SAC                                                                                 |
| Descriptif de CUL - Descriptif de LUC                                                                                 |
+-----------------------------------------------------------------------------------------------------------------------+
--------------
drop table if exists `anagramme`
--------------
 
--------------
create table `anagramme` as
  select  max(code_1) as code_1,
          max(mess_1) as mess_1,
          max(code_2) as code_2,
          max(mess_2) as mess_2,
          max(code_3) as code_3,
          max(mess_3) as mess_3,
          max(code_4) as code_4,
          max(mess_4) as mess_4,
          max(code_5) as code_5,
          max(mess_5) as mess_5,
          max(code_6) as code_6,
          max(mess_6) as mess_6
    from  (  select  case rang when 1 then code else '' end as code_1,
                     case rang when 1 then mess else '' end as mess_1,
                     case rang when 2 then code else '' end as code_2,
                     case rang when 2 then mess else '' end as mess_2,
                     case rang when 3 then code else '' end as code_3,
                     case rang when 3 then mess else '' end as mess_3,
                     case rang when 4 then code else '' end as code_4,
                     case rang when 4 then mess else '' end as mess_4,
                     case rang when 5 then code else '' end as code_5,
                     case rang when 5 then mess else '' end as mess_5,
                     case rang when 6 then code else '' end as code_6,
                     case rang when 6 then mess else '' end as mess_6,
                     lien_1
               from  (  select  *,
                                count(lien_2)+1 as rang
                          from  (  select  t1.code as code,
                                           t1.mess as mess,
                                           t1.lien as lien_1,
                                           t2.lien as lien_2
                                     from  test    as t1
                          left outer join  test    as t2
                                       on  t2.lien = t1.lien
                                      and  t2.code < t1.code
                                 order by  t1.lien, t1.code
                                )  as x
                      group by  lien_1, code
                     )  as y
          ) as z
group by  lien_1
--------------
 
--------------
select * from `anagramme`
--------------
 
+--------+-------------------+--------+-------------------+--------+-------------------+--------+-------------------+--------+-------------------+--------+-------------------+
| code_1 | mess_1            | code_2 | mess_2            | code_3 | mess_3            | code_4 | mess_4            | code_5 | mess_5            | code_6 | mess_6            |
+--------+-------------------+--------+-------------------+--------+-------------------+--------+-------------------+--------+-------------------+--------+-------------------+
| abc    | Descriptif de ABC | acb    | Descriptif de ACB | bac    | Descriptif de BAC | bca    | Descriptif de BCA | cab    | Descriptif de CAB | cba    | Descriptif de CBA |
| cas    | Descriptif de CAS | sac    | Descriptif de SAC |        |                   |        |                   |        |                   |        |                   |
| cul    | Descriptif de CUL | luc    | Descriptif de LUC |        |                   |        |                   |        |                   |        |                   |
| blo    | Descriptif de BLO | bol    | Descriptif de BOL | lob    | Descriptif de LOB |        |                   |        |                   |        |                   |
+--------+-------------------+--------+-------------------+--------+-------------------+--------+-------------------+--------+-------------------+--------+-------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

**Artemus24** · 16/03/2017, 05h58

Salut boazee.

Voici l'algorithme dont j'ai parlé ci-avant, sous la forme d'une procédure stockée.
J'ai pris comme exemple des mots de 5 caractères.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`     integer unsigned  not null auto_increment primary key,
  `code`   char(05)          not null,
  `lien`   integer unsigned      null default null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP PROCEDURE IF EXISTS `trait`
--------------
 
--------------
CREATE PROCEDURE `trait`
( IN    _mot  char(05),
  IN    _ind  tinyint unsigned,
  IN    _long tinyint unsigned,
  INOUT _lien integer unsigned
)
DETERMINISTIC
NO SQL
BEGIN
DECLARE _2nd TINYINT UNSIGNED;
 
IF (_ind >= _long) THEN
   SELECT id FROM test WHERE code = _mot AND id < _lien INTO _lien;
ELSE
  SET _2nd = _ind;
  WHILE (_2nd <= _long)
  DO
    IF (_2nd >_ind) THEN
       SET _mot = concat(left(_mot,_ind-1),substring(_mot,_2nd,1),substring(_mot,_ind+1,(_2nd-_ind-1)),substring(_mot,_ind,1),right(_mot,_long-_2nd));
    END IF;
 
    CALL trait(_mot,_ind+1,_long,_lien);
 
    IF (_2nd>_ind) THEN
       SET _mot = concat(left(_mot,_ind-1),substring(_mot,_2nd,1),substring(_mot,_ind+1,(_2nd-_ind-1)),substring(_mot,_ind,1),right(_mot,_long-_2nd));
    END IF;
 
    SET _2nd = _2nd + 1;
  END WHILE;
END IF;
END
--------------
 
--------------
DROP TRIGGER IF EXISTS `anagramme`
--------------
 
--------------
CREATE TRIGGER `anagramme`
BEFORE INSERT ON `test`
FOR EACH ROW
BEGIN
  DECLARE _lien integer unsigned;
  SET _lien = coalesce((select max(id)+1 from test), 1);
  call trait(new.code,1,5,_lien);
  SET new.lien = _lien;
END
--------------
 
--------------
insert into `test` (`code`) values
('arbre'),('barre'),('caser'),('chien'),('chine'),('coupe'),('dates'),('demon'),('cesar'),
('desir'),('diete'),('digue'),('entre'),('garde'),('grade'),('guide'),('ligne'),('remet'),
('linge'),('loupe'),('metre'),('monde'),('niche'),('notre'),('opter'),('parmi'),('acres'),
('piton'),('point'),('porte'),('pouce'),('poule'),('prima'),('rente'),('rever'),('egard'),
('riant'),('rides'),('sacre'),('signe'),('singe'),('situe'),('sorte'),('stade'),('races'),
('store'),('suite'),('tenor'),('terme'),('tiede'),('train'),('verre'),('voter'),('votre')
--------------
 
--------------
select * from `test`
--------------
 
+----+-------+------+
| id | code  | lien |
+----+-------+------+
|  1 | arbre |    1 |
|  2 | barre |    1 |
|  3 | caser |    3 |
|  4 | chien |    4 |
|  5 | chine |    4 |
|  6 | coupe |    6 |
|  7 | dates |    7 |
|  8 | demon |    8 |
|  9 | cesar |    3 |
| 10 | desir |   10 |
| 11 | diete |   11 |
| 12 | digue |   12 |
| 13 | entre |   13 |
| 14 | garde |   14 |
| 15 | grade |   14 |
| 16 | guide |   12 |
| 17 | ligne |   17 |
| 18 | remet |   18 |
| 19 | linge |   17 |
| 20 | loupe |   20 |
| 21 | metre |   18 |
| 22 | monde |    8 |
| 23 | niche |    4 |
| 24 | notre |   24 |
| 25 | opter |   25 |
| 26 | parmi |   26 |
| 27 | acres |    3 |
| 28 | piton |   28 |
| 29 | point |   28 |
| 30 | porte |   25 |
| 31 | pouce |    6 |
| 32 | poule |   20 |
| 33 | prima |   26 |
| 34 | rente |   13 |
| 35 | rever |   35 |
| 36 | egard |   14 |
| 37 | riant |   37 |
| 38 | rides |   10 |
| 39 | sacre |    3 |
| 40 | signe |   40 |
| 41 | singe |   40 |
| 42 | situe |   42 |
| 43 | sorte |   43 |
| 44 | stade |    7 |
| 45 | races |    3 |
| 46 | store |   43 |
| 47 | suite |   42 |
| 48 | tenor |   24 |
| 49 | terme |   18 |
| 50 | tiede |   11 |
| 51 | train |   37 |
| 52 | verre |   35 |
| 53 | voter |   53 |
| 54 | votre |   53 |
+----+-------+------+
--------------
select  group_concat(code order by code  separator ' - ') as code
    from  test
group by  lien
--------------
 
+---------------------------------------+
| code                                  |
+---------------------------------------+
| arbre - barre                         |
| acres - caser - cesar - races - sacre |
| chien - chine - niche                 |
| coupe - pouce                         |
| dates - stade                         |
| demon - monde                         |
| desir - rides                         |
| diete - tiede                         |
| digue - guide                         |
| entre - rente                         |
| egard - garde - grade                 |
| ligne - linge                         |
| metre - remet - terme                 |
| loupe - poule                         |
| notre - tenor                         |
| opter - porte                         |
| parmi - prima                         |
| piton - point                         |
| rever - verre                         |
| riant - train                         |
| signe - singe                         |
| situe - suite                         |
| sorte - store                         |
| voter - votre                         |
+---------------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

**Boazee** · 16/03/2017, 11h24

Salut Artemus...

Encore merci pour ta dévotion à la tache...

Je n'ai pas le temps là mais je tache de regarder tut ça de plus près dès que possible... et je reviendrai probablement avec des questions.

je pense que cette partie va beaucoup me plaire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
--------------
DROP PROCEDURE IF EXISTS `trait`
--------------
 
--------------
CREATE PROCEDURE `trait`
( IN    _mot  char(05),
  IN    _ind  tinyint unsigned,
  IN    _long tinyint unsigned,
  INOUT _lien integer unsigned
)
DETERMINISTIC
NO SQL
BEGIN
DECLARE _2nd TINYINT UNSIGNED;
 
IF (_ind >= _long) THEN
   SELECT id FROM test WHERE code = _mot AND id < _lien INTO _lien;
ELSE
  SET _2nd = _ind;
  WHILE (_2nd <= _long)
  DO
    IF (_2nd >_ind) THEN
       SET _mot = concat(left(_mot,_ind-1),substring(_mot,_2nd,1),substring(_mot,_ind+1,(_2nd-_ind-1)),substring(_mot,_ind,1),right(_mot,_long-_2nd));
    END IF;
 
    CALL trait(_mot,_ind+1,_long,_lien);
 
    IF (_2nd>_ind) THEN
       SET _mot = concat(left(_mot,_ind-1),substring(_mot,_2nd,1),substring(_mot,_ind+1,(_2nd-_ind-1)),substring(_mot,_ind,1),right(_mot,_long-_2nd));
    END IF;
 
    SET _2nd = _2nd + 1;
  END WHILE;
END IF;
END
--------------
 
--------------
DROP TRIGGER IF EXISTS `anagramme`
--------------
 
--------------
CREATE TRIGGER `anagramme`
BEFORE INSERT ON `test`
FOR EACH ROW
BEGIN
  DECLARE _lien integer unsigned;
  SET _lien = coalesce((select max(id)+1 from test), 1);
  call trait(new.code,1,5,_lien);
  SET new.lien = _lien;
END
--------------

Car c'est ce que j'ai tenté d'étudier seul Hier soir et il devait y avoir plein d'erreurs de ma part car je n'ai rien réussi à sortir de concret... en fait si, j'avais réussi à écrire des procédures et fonctions qui fonctionnaient en en déclarant des variables, mais après impossible d'arriver à l'appliquer à une table... j'ai essayé puis j'ai craqué et laissé tomber.

Je vais analyser soigneusement le code que tu me propose et tacher d'en comprendre chaque ligne...je sens que je vais apprendre plein de trucs encore.

Merci donc pour ton script. James west serait fier de toi

**Boazee** · 17/03/2017, 10h00

Bonjour Artemus24

j'ai essayé ta suite de script...
créé la base "base" --> OK
créé ta table "test" --> OK

mais au niveau de la procédure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CREATE PROCEDURE `trait`
( IN    _mot  char(05),
  IN    _ind  tinyint unsigned,
  IN    _long tinyint unsigned,
  INOUT _lien integer unsigned
)
DETERMINISTIC
NO SQL
BEGIN
DECLARE _2nd TINYINT UNSIGNED;
 
IF (_ind >= _long) THEN
   SELECT id FROM test WHERE code = _mot AND id < _lien INTO _lien;
ELSE
  SET _2nd = _ind;
  WHILE (_2nd <= _long)
  DO
    IF (_2nd >_ind) THEN
       SET _mot = concat(left(_mot,_ind-1),substring(_mot,_2nd,1),substring(_mot,_ind+1,(_2nd-_ind-1)),substring(_mot,_ind,1),right(_mot,_long-_2nd));
    END IF;
 
    CALL trait(_mot,_ind+1,_long,_lien);
 
    IF (_2nd>_ind) THEN
       SET _mot = concat(left(_mot,_ind-1),substring(_mot,_2nd,1),substring(_mot,_ind+1,(_2nd-_ind-1)),substring(_mot,_ind,1),right(_mot,_long-_2nd));
    END IF;
 
    SET _2nd = _2nd + 1;
  END WHILE;
END IF;
END

cela me renvoi l'erreur suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 10

la ligne 10 étant apparemment celle ci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DECLARE _2nd TINYINT UNSIGNED;

n'étant qu'a mes débuts d'exploration de ce genre de script je suis un peu perdu... et j'aurai bien aimé voir le résultat ce que ça donne lorsque ça fonctionne.

Merci par avance (note : y'a vraiment pas d'urgence j'ai tout mon temps... en parallèle j'étudie le système de fonction et c'est vraiment cool et assez ressemblant au php dans le fond, même si la forme est différente)

**Artemus24** · 17/03/2017, 11h39

Salut boazee.

Je vous donne le source du script mysql. Je pense que vous avez dû oublier de faire quelque chose lors de la déclaration de la procédure stockée.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
SET AUTOCOMMIT = 0;
START TRANSACTION;
 
-- ======================
-- Base de Données `base`
-- ======================
 
DROP DATABASE IF EXISTS `base`;
 
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`;
 
use `base`;
 
-- ============
-- Table `test`
-- ============
 
DROP TABLE IF EXISTS `test`;
 
CREATE TABLE `test`
( `id`     integer unsigned  not null auto_increment primary key,
  `code`   char(05)          not null,
  `lien`   integer unsigned      null default null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED;
 
-- ===============
-- Function `swap`
-- ===============
 
drop function if exists `swap`;
 
DELIMITER $$
 
CREATE FUNCTION `swap`
( `_mot`  char(05),
  `_one`  tinyint unsigned,
  `_two`  tinyint unsigned
) RETURNS char(05)
  DETERMINISTIC
  LANGUAGE SQL
BEGIN
  RETURN CONCAT(LEFT(_mot,_one-1),SUBSTRING(_mot,_two,1),SUBSTRING(_mot,_one+1,(_two-_one-1)),SUBSTRING(_mot,_one,1),RIGHT(_mot,5-_two));
END$$
 
DELIMITER ;
 
-- =========================
-- Procédure stockée `trait`
-- =========================
 
DROP PROCEDURE IF EXISTS `trait`;
 
DELIMITER $$
CREATE PROCEDURE `trait`
( IN    _mot  char(05),
  IN    _ind  tinyint unsigned,
  IN    _long tinyint unsigned,
  INOUT _lien integer unsigned
)
DETERMINISTIC
NO SQL
BEGIN
DECLARE _2nd TINYINT UNSIGNED;
 
IF (_ind >= _long) THEN
   SELECT id FROM test WHERE code = _mot AND id < _lien INTO _lien;
ELSE
  SET _2nd = _ind;
  WHILE (_2nd <= _long)
  DO
    IF (_2nd >_ind) THEN SET _mot = swap(_mot,_ind,_2nd); END IF;
    CALL trait(_mot,_ind+1,_long,_lien);
    IF (_2nd >_ind) THEN SET _mot = swap(_mot,_ind,_2nd); END IF;
    SET _2nd = _2nd + 1;
  END WHILE;
END IF;
END$$
DELIMITER ;
 
-- ===================
-- Trigger `anagramme`
-- ===================
 
DROP TRIGGER IF EXISTS `anagramme`;
 
DELIMITER $$
CREATE TRIGGER `anagramme`
BEFORE INSERT ON `test`
FOR EACH ROW
BEGIN
  DECLARE _lien integer unsigned;
  SET _lien = coalesce((select max(id)+1 from test), 1);
  call trait(new.code,1,5,_lien);
  SET new.lien = _lien;
END$$
DELIMITER ;
 
-- =====================
-- Insertion dans `test`
-- =====================
 
insert into `test` (`code`) values
('arbre'),('barre'),('caser'),('chien'),('chine'),('coupe'),('dates'),('demon'),('cesar'),
('desir'),('diete'),('digue'),('entre'),('garde'),('grade'),('guide'),('ligne'),('remet'),
('linge'),('loupe'),('metre'),('monde'),('niche'),('notre'),('opter'),('parmi'),('acres'),
('piton'),('point'),('porte'),('pouce'),('poule'),('prima'),('rente'),('rever'),('egard'),
('riant'),('rides'),('sacre'),('signe'),('singe'),('situe'),('sorte'),('stade'),('races'),
('store'),('suite'),('tenor'),('terme'),('tiede'),('train'),('verre'),('voter'),('votre');
 
-- ================
-- Vidage de `test`
-- ================
 
select * from `test`;
 
-- ============
-- Requête N°1
-- ============
 
  select  group_concat(code order by code  separator ' - ') as code
    from  test
group by  lien;
 
-- ===
-- Fin
-- ===
 
COMMIT;
SET AUTOCOMMIT = 1;
exit

Et voici son exécution :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
    DEFAULT CHARACTER SET `latin1`
    DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `id`     integer unsigned  not null auto_increment primary key,
  `code`   char(05)          not null,
  `lien`   integer unsigned      null default null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
drop function if exists `swap`
--------------
 
--------------
CREATE FUNCTION `swap`
( `_mot`  char(05),
  `_one`  tinyint unsigned,
  `_two`  tinyint unsigned
) RETURNS char(05)
  DETERMINISTIC
  LANGUAGE SQL
BEGIN
  RETURN CONCAT(LEFT(_mot,_one-1),SUBSTRING(_mot,_two,1),SUBSTRING(_mot,_one+1,(_two-_one-1)),SUBSTRING(_mot,_one,1),RIGHT(_mot,5-_two));
END
--------------
 
--------------
DROP PROCEDURE IF EXISTS `trait`
--------------
 
--------------
CREATE PROCEDURE `trait`
( IN    _mot  char(05),
  IN    _ind  tinyint unsigned,
  IN    _long tinyint unsigned,
  INOUT _lien integer unsigned
)
DETERMINISTIC
NO SQL
BEGIN
DECLARE _2nd TINYINT UNSIGNED;
 
IF (_ind >= _long) THEN
   SELECT id FROM test WHERE code = _mot AND id < _lien INTO _lien;
ELSE
  SET _2nd = _ind;
  WHILE (_2nd <= _long)
  DO
    IF (_2nd >_ind) THEN SET _mot = swap(_mot,_ind,_2nd); END IF;
    CALL trait(_mot,_ind+1,_long,_lien);
    IF (_2nd >_ind) THEN SET _mot = swap(_mot,_ind,_2nd); END IF;
    SET _2nd = _2nd + 1;
  END WHILE;
END IF;
END
--------------
 
--------------
DROP TRIGGER IF EXISTS `anagramme`
--------------
 
--------------
CREATE TRIGGER `anagramme`
BEFORE INSERT ON `test`
FOR EACH ROW
BEGIN
  DECLARE _lien integer unsigned;
  SET _lien = coalesce((select max(id)+1 from test), 1);
  call trait(new.code,1,5,_lien);
  SET new.lien = _lien;
END
--------------
 
--------------
insert into `test` (`code`) values
('arbre'),('barre'),('caser'),('chien'),('chine'),('coupe'),('dates'),('demon'),('cesar'),
('desir'),('diete'),('digue'),('entre'),('garde'),('grade'),('guide'),('ligne'),('remet'),
('linge'),('loupe'),('metre'),('monde'),('niche'),('notre'),('opter'),('parmi'),('acres'),
('piton'),('point'),('porte'),('pouce'),('poule'),('prima'),('rente'),('rever'),('egard'),
('riant'),('rides'),('sacre'),('signe'),('singe'),('situe'),('sorte'),('stade'),('races'),
('store'),('suite'),('tenor'),('terme'),('tiede'),('train'),('verre'),('voter'),('votre')
--------------
 
--------------
select * from `test`
--------------
 
+----+-------+------+
| id | code  | lien |
+----+-------+------+
|  1 | arbre |    1 |
|  2 | barre |    1 |
|  3 | caser |    3 |
|  4 | chien |    4 |
|  5 | chine |    4 |
|  6 | coupe |    6 |
|  7 | dates |    7 |
|  8 | demon |    8 |
|  9 | cesar |    3 |
| 10 | desir |   10 |
| 11 | diete |   11 |
| 12 | digue |   12 |
| 13 | entre |   13 |
| 14 | garde |   14 |
| 15 | grade |   14 |
| 16 | guide |   12 |
| 17 | ligne |   17 |
| 18 | remet |   18 |
| 19 | linge |   17 |
| 20 | loupe |   20 |
| 21 | metre |   18 |
| 22 | monde |    8 |
| 23 | niche |    4 |
| 24 | notre |   24 |
| 25 | opter |   25 |
| 26 | parmi |   26 |
| 27 | acres |    3 |
| 28 | piton |   28 |
| 29 | point |   28 |
| 30 | porte |   25 |
| 31 | pouce |    6 |
| 32 | poule |   20 |
| 33 | prima |   26 |
| 34 | rente |   13 |
| 35 | rever |   35 |
| 36 | egard |   14 |
| 37 | riant |   37 |
| 38 | rides |   10 |
| 39 | sacre |    3 |
| 40 | signe |   40 |
| 41 | singe |   40 |
| 42 | situe |   42 |
| 43 | sorte |   43 |
| 44 | stade |    7 |
| 45 | races |    3 |
| 46 | store |   43 |
| 47 | suite |   42 |
| 48 | tenor |   24 |
| 49 | terme |   18 |
| 50 | tiede |   11 |
| 51 | train |   37 |
| 52 | verre |   35 |
| 53 | voter |   53 |
| 54 | votre |   53 |
+----+-------+------+
--------------
select  group_concat(code order by code  separator ' - ') as code
    from  test
group by  lien
--------------
 
+---------------------------------------+
| code                                  |
+---------------------------------------+
| arbre - barre                         |
| acres - caser - cesar - races - sacre |
| chien - chine - niche                 |
| coupe - pouce                         |
| dates - stade                         |
| demon - monde                         |
| desir - rides                         |
| diete - tiede                         |
| digue - guide                         |
| entre - rente                         |
| egard - garde - grade                 |
| ligne - linge                         |
| metre - remet - terme                 |
| loupe - poule                         |
| notre - tenor                         |
| opter - porte                         |
| parmi - prima                         |
| piton - point                         |
| rever - verre                         |
| riant - train                         |
| signe - singe                         |
| situe - suite                         |
| sorte - store                         |
| voter - votre                         |
+---------------------------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 1
--------------
 
Appuyez sur une touche pour continuer...

@+

Besoin d'aide pour une requête de recherche d'anagramme dans une table [MariaDB]

Requêtes MySQL

Discussions similaires

Partager

Partager