Jointure : SELECT dans SELECT + GROUP : déterminez si dernière version

**Feneck91** · 03/12/2016, 09h09

Bonjour à tous et à toutes....
J'utilise ce site pour tester : https://www.tutorialspoint.com/execute_sql_online.php
Ce que je demande là, je l'ai codé en Linq sous EntityFramework mais là, en SQL c'est un peu plus compliqué...

Voilà le problème : j'ai deux tables, jusque là c'est simple...
Table : Table commune :

Code :

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

CREATE TABLE Common(CommonID integer PRIMARY KEY, Name text, Version integer,Color text);

Elle contient :

1    |    Tom    |    1    |    rouge
2    |    Lucy   |    1    |    vert
3    |    Frank  |    1    |    bleu
4    |    Tom    |    3    |    yellow
5    |    Bob    |    1    |    blue
6    |    Lucy   |    3    |    black
7    |    Tom    |    2    |    purple
8    |    Lucy   |    2    |    orange
9    |    Frank  |    2    |    marron

Deuxième table :

Code :

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

1
2
3
4
5
6
CREATE TABLE MyTable(MyTableID integer PRIMARY KEY, AAA text, FKCommonID integer,
    CONSTRAINT FK_CommonID_Common FOREIGN KEY (FKCommonID)
        REFERENCES Common(CommonID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
    );

11    |    Pluc    |    1
12    |    Plog    |    3
13    |    Toci    |    6
14    |    Pouc    |    9
15    |    Bloc    |    3
16    |    Plou    |    9

Une requête simple pour récupérer les informations via une jointure :

Code :

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

1
2
3
4
5
6
7
SELECT MyTable.MyTableID as ID,
       MyTable.AAA as Pseudo,
       Common.Name as Nom,
       Common.Version as Version,
       Common.Color as Couleur
FROM MyTable
JOIN Common ON MyTable.FKCommonID = Common.CommonID;

Résultat :

11    |    Pluc    |    Tom      |    1    |    rouge
12    |    Plog    |    Frank    |    1    |    bleu
13    |    Toci    |    Lucy     |    3    |    black
14    |    Pouc    |    Frank    |    2    |    marron
15    |    Bloc    |    Frank    |    1    |    bleu
16    |    Plou    |    Frank    |    2    |    marron

Bon jusque là rien de bien compliqué...
Là où cela se complique, c'est qu'en fait, le champ Common.Name est un champ contenant une version. Lorsque l'on récupère une ligne, j'aimerais savoir, si pour ce champ Nom, la ligne affiché est la version la plus grande de toutes les lignes qui ont ce nom.
En fait je référence des éléments dont les noms sont les clefs (en quelque sorte) et où chaque élément est versionné : aucun trou entre les versions, les versions commencent à 1.
Et ce qu'on veut savoir c'est si la donnée est la dernière version ou pas.

Typiquement, j'aimerais une requête qui me retourne :

11    |    Pluc    |    Tom      |    1    |    rouge      |    False (ici la dernière version de Tom est '3' et cette ligne pointe sur un Tom dont la version est '1'
12    |    Plog    |    Frank    |    1    |    bleu       |    False (ici la dernière version de Franck est '2' et cette ligne pointe sur un Franck dont la version est '1'
13    |    Toci    |    Lucy     |    3    |    black      |    True  (ici la dernière version de Lucy est '3' et cette ligne pointe sur un Lucy dont la version est '3'
14    |    Pouc    |    Frank    |    2    |    marron     |    True  (ici la dernière version de Franck est '2' et cette ligne pointe sur un Franck dont la version est '2'
15    |    Bloc    |    Frank    |    1    |    bleu       |    False (ici la dernière version de Franck est '2' et cette ligne pointe sur un Franck dont la version est '1'
16    |    Plou    |    Frank    |    2    |    marron     |    True  (ici la dernière version de Franck est '2' et cette ligne pointe sur un Franck dont la version est '2'

Le première étape consisterait à récupérer le MAX des versions via un GROUP BY NAME.
Déjà là je pèche : le WHERE Name = Nom, Nom est inconnu mais c'est ça l'idée => grouper par le nom, le nom qui est le résultat de la requête avec la jointure...

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
SELECT MyTable.MyTableID as ID,
       MyTable.AAA as Pseudo,
       Common.Name as Nom,
       Common.Version as Version,
       Common.Color as Couleur,
       (SELECT MAX(Version)
        FROM Common
        JOIN MyTable
        ON Common.CommonID = MyTable.FKCommonID
        WHERE Name = Nom
        GROUP BY Name
) As MaxVersion
FROM MyTable
JOIN Common
ON MyTable.FKCommonID = Common.CommonID;

Après l'idée, est que si j'ai le MAX de la version, je devrais pouvoir avoir une colonne de type bool avec Version = MaxVersion qui me donnerait true ou False....
Je vous donne tout le code que j'ai écris sur le site pour pouvoir tester :

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
DROP TABLE IF EXISTS Common;
DROP TABLE IF EXISTS MyTable;
 
BEGIN TRANSACTION;
 
/* Création de la table Common */
CREATE TABLE Common(CommonID integer PRIMARY KEY, Name text, Version integer,Color text);
 
/* Create few records in this table */
INSERT INTO Common VALUES(1,'Tom' ,  1, 'rouge');
INSERT INTO Common VALUES(2,'Lucy',  1, 'vert');
INSERT INTO Common VALUES(3,'Frank', 1, 'bleu');
INSERT INTO Common VALUES(4,'Tom',   3, 'yellow');
INSERT INTO Common VALUES(5,'Bob',   1, 'blue');
INSERT INTO Common VALUES(6,'Lucy',  3, 'black');
INSERT INTO Common VALUES(7,'Tom',   2, 'purple');
INSERT INTO Common VALUES(8,'Lucy',  2, 'orange');
INSERT INTO Common VALUES(9,'Frank', 2, 'marron');
 
/* Création de la table MyTable */
CREATE TABLE MyTable(MyTableID integer PRIMARY KEY, AAA text, FKCommonID integer,
    CONSTRAINT FK_CommonID_Common FOREIGN KEY (FKCommonID)
        REFERENCES Common(CommonID)
        ON DELETE CASCADE    
        ON UPDATE CASCADE    
    );
 
INSERT INTO MyTable VALUES(11, 'Pluc' , 1);
INSERT INTO MyTable VALUES(12, 'Plog',  3);
INSERT INTO MyTable VALUES(13, 'Toci',  6);
INSERT INTO MyTable VALUES(14, 'Pouc',  9);
INSERT INTO MyTable VALUES(15, 'Bloc',  3);
INSERT INTO MyTable VALUES(16, 'Plou',  9);
 
COMMIT;
 
/* Vérification que la clef étrangère fonctionne bien 
DELETE FROM Common WHERE Name = 'Frank';
*/
 
/* Display all the records from the table */
/*
SELECT MyTable.MyTableID as ID,
       MyTable.AAA as Pseudo,
       Common.Name as Nom,
       Common.Version as Version,
       Common.Color as Couleur
FROM MyTable
JOIN Common ON MyTable.FKCommonID = Common.CommonID;
*/
/*
SELECT *, MAX(Version)
        FROM Common
        GROUP BY Name;
*/
 
SELECT MyTable.MyTableID as ID,
       MyTable.AAA as Pseudo,
       Common.Name as Nom,
       Common.Version as Version,
       Common.Color as Couleur,
       (SELECT MAX(Version)
        FROM Common
        JOIN MyTable
        ON Common.CommonID = MyTable.FKCommonID
        WHERE Name = Nom
        GROUP BY Name
) As MaxVersion
FROM MyTable
JOIN Common
ON MyTable.FKCommonID = Common.CommonID;

Voilà, s'il y a des cadors ici, merci de votre aide !

**Feneck91** · 03/12/2016, 09h33

Bon, à priori j'ai trouvé :

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
 
SELECT MyTable.MyTableID as ID,
       MyTable.AAA as Pseudo,
       Common.Name as Nom,
       Common.Version as Version,
       Common.Color as Couleur,
       (SELECT MaxVersion FROM
           (SELECT * FROM (SELECT Name, MAX(Version) as MaxVersion
                           FROM Common
                           GROUP BY Name
                          )
            WHERE Name = Common.Name
           )
        ) = Common.Version
FROM MyTable
JOIN Common
ON MyTable.FKCommonID = Common.CommonID;

**vttman** · 03/12/2016, 14h52

Bonjour,
Je pense que cette partie

Code :

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

1
2
3
4
5
6
7
8
9
10
 
(SELECT MaxVersion FROM
           (SELECT * FROM 
                       (SELECT Name, MAX(Version) as MaxVersion
                                   FROM Common
                                   GROUP BY Name
                       )
            WHERE Name = Common.Name
           )
)

peut se simplifier en cela ... à tester
[EDIT] Group by et clause where à inverser ... Merci à tatayo

Code :

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

1
2
3
4
5
6
 
(SELECT MAX(Version) as MaxVersion
            FROM Common
            GROUP BY Name
            WHERE Name = Common.Name
)

**tatayo** · 03/12/2016, 15h59

Bonjour,

Code sql :

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

1
2
3
4
5
 
SELECT MAX(Version) as MaxVersion
            FROM Common
            GROUP BY Name
            WHERE Name = Common.Name

Cette requête ne peut pas fonctionner, car le GROUP BY doit être après le WHERE. Et d'ailleurs, à quoi sert-il, vu que name n'est pas dans le SELECT ?

Tatayo.

**vttman** · 03/12/2016, 16h39

Envoyé par tatayo

Bonjour,

Code sql :

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

1
2
3
4
5
 
SELECT MAX(Version) as MaxVersion
            FROM Common
            GROUP BY Name
            WHERE Name = Common.Name

Cette requête ne peut pas fonctionner, car le GROUP BY doit être après le WHERE. Et d'ailleurs, à quoi sert-il, vu que name n'est pas dans le SELECT ?

Tatayo.

Oui bon une inversion et on n'en parle plus (distraction), l'idée est de regrouper les lignes par name et de sélectionner au passage (par name) la valeur maximum de version,
maintenant si ça ne marche pas ... tant pis pour la tentative de simplification ....

**tatayo** · 03/12/2016, 19h14

En fait la "recherche du dernier" revient très souvent sur le forum.
On peut la faire avec une jointure externe, une sous-requête comme ici ou avec un NOT EXISTS... Personnellement je penche pour la jointure externe.
Par exemple ici, je ferai ceci:

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT MyTable.MyTableID as ID,
       MyTable.AAA as Pseudo,
       Common.Name as Nom,
       Common.Version as Version,
       Common.Color as Couleur
FROM MyTable
JOIN Common ON MyTable.FKCommonID = Common.CommonID
left outer join common as c2 on MyTable.FKCommonID = c2.CommonID
 and c2.version > common.version
where c2.CommonId is null

Tatayo.

**Feneck91** · 03/12/2016, 19h44

Merci pour vos réponses...

Ça me donne exactement le résultat inverse... Mais ça c'est un coup de chance...
Je modifie le code et vous en redonne un autre avec d'autres lignes et là ça donne juste n'importe quoi.

En plus le WHERE Name = Common.Name ne sert à rien, forcément Name = Name !
Le fait de le faire en deux requêtes : requête 1 fait sur toutes la table
La SELECT MaxVersion FROM sélectionne le nom par rapport à la ligne de la requête courante (celle de la jointure) ce qui n'est pas le cas quand le where est à l'intérieur du select le plus imbriqué (en tout cas c'est ce que je pense)
voici le code à tester et le résultat n'est pas le même !
Merci pour vos réponses.

Autres suggestions ?

Nouveau 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
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
DROP TABLE IF EXISTS Common;
DROP TABLE IF EXISTS MyTable;
 
BEGIN TRANSACTION;
 
/* Création de la table Common */
CREATE TABLE Common(CommonID integer PRIMARY KEY, Name text, Version integer,Color text);
 
/*
    Max version = Bob   : 1
    Max version = Tom   : 4
    Max version = Lucy  : 3
    Max version = Frank : 2
*/
 
/* Create few records in this table */
INSERT INTO Common VALUES(1, 'Tom' ,  1, 'rouge');
INSERT INTO Common VALUES(2, 'Lucy',  1, 'vert');
INSERT INTO Common VALUES(3, 'Frank', 1, 'bleu');
INSERT INTO Common VALUES(4, 'Tom',   3, 'yellow');
INSERT INTO Common VALUES(5, 'Bob',   1, 'blue');
INSERT INTO Common VALUES(6, 'Tom',   4, 'rose');
INSERT INTO Common VALUES(7, 'Lucy',  3, 'black');
INSERT INTO Common VALUES(8, 'Tom',   2, 'purple');
INSERT INTO Common VALUES(9, 'Lucy',  2, 'orange');
INSERT INTO Common VALUES(10,'Frank', 2, 'marron');
 
/* Création de la table MyTable */
CREATE TABLE MyTable(MyTableID integer PRIMARY KEY, AAA text, FKCommonID integer,
    CONSTRAINT FK_CommonID_Common FOREIGN KEY (FKCommonID)
        REFERENCES Common(CommonID)
        ON DELETE CASCADE    
        ON UPDATE CASCADE    
    );
 
INSERT INTO MyTable VALUES(11, 'Pluc' , 1);
INSERT INTO MyTable VALUES(12, 'Plog',  3);
INSERT INTO MyTable VALUES(13, 'Toci',  6);
INSERT INTO MyTable VALUES(14, 'Tpci',  6);
INSERT INTO MyTable VALUES(15, 'To9i',  6);
INSERT INTO MyTable VALUES(16, 'Pouc',  9);
INSERT INTO MyTable VALUES(17, 'Plou',  9);
INSERT INTO MyTable VALUES(18, 'Bloc',  3);
INSERT INTO MyTable VALUES(19, 'Pluf',  6);
 
COMMIT;
 
/* Vérification que la clef étrangère fonctionne bien 
DELETE FROM Common WHERE Name = 'Frank';
*/
 
SELECT MyTable.MyTableID as ID,
       MyTable.AAA as Pseudo,
       Common.Name as Nom,
       Common.Color as Couleur,
       Common.Version as Version,
       (SELECT MaxVersion FROM
           (SELECT * FROM (SELECT Name, MAX(Version) as MaxVersion
                           FROM Common
                           GROUP BY Name
                          )
            WHERE Name = Common.Name
           )
        ),
       (SELECT MaxVersion FROM
           (SELECT * FROM (SELECT Name, MAX(Version) as MaxVersion
                           FROM Common
                           GROUP BY Name
                          )
            WHERE Name = Common.Name
           )
        ) = Common.Version
FROM MyTable
JOIN Common
ON MyTable.FKCommonID = Common.CommonID;
 
SELECT Name, MAX(Version) as MaxVersion
            FROM Common
            GROUP BY Name
ORDER BY Name ASC;
 
SELECT MyTable.MyTableID as ID,
       MyTable.AAA as Pseudo,
       Common.Name as Nom,
       Common.Color as Couleur,
       Common.Version as Version,
       (SELECT MAX(Version) as MaxVersion
            FROM Common
            WHERE Name = Common.Name
            GROUP BY Name
        ),
       (SELECT MAX(Version) as MaxVersion
            FROM Common
            WHERE Name = Common.Name
            GROUP BY Name
        ) = Common.Version
FROM MyTable
JOIN Common
ON MyTable.FKCommonID = Common.CommonID;

Résultat :

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
--------------------------------------------------------------------------------------
 ID   |    AAA     |    Name   |    Color   | Version | Max Version | IsMAxVersion ?    
--------------------------------------------------------------------------------------
11    |    Pluc    |    Tom    |    rouge   |    1    |      4      |       0
12    |    Plog    |    Frank  |    bleu    |    1    |      2      |       0
13    |    Toci    |    Tom    |    rose    |    4    |      4      |       1
14    |    Tpci    |    Tom    |    rose    |    4    |      4      |       1
15    |    To9i    |    Tom    |    rose    |    4    |      4      |       1
16    |    Pouc    |    Lucy   |    orange  |    2    |      3      |       0
17    |    Plou    |    Lucy   |    orange  |    2    |      3      |       0
18    |    Bloc    |    Frank  |    bleu    |    1    |      2      |       0
19    |    Pluf    |    Tom    |    rose    |    4    |      4      |       1
 
Max version par Name :
----------------------
Bob    |    1
Frank  |    2
Lucy   |    3
Tom    |    4
 
--------------------------------------------------------------------------------------
 ID   |    AAA     |    Name   |    Color   | Version | Max Version | IsMAxVersion ?    
--------------------------------------------------------------------------------------
11    |    Pluc    |    Tom    |    rouge   |    1    |      1      |       1
12    |    Plog    |    Frank  |    bleu    |    1    |      1      |       1
13    |    Toci    |    Tom    |    rose    |    4    |      1      |       0
14    |    Tpci    |    Tom    |    rose    |    4    |      1      |       0
15    |    To9i    |    Tom    |    rose    |    4    |      1      |       0
16    |    Pouc    |    Lucy   |    orange  |    2    |      1      |       0
17    |    Plou    |    Lucy   |    orange  |    2    |      1      |       0
18    |    Bloc    |    Frank  |    bleu    |    1    |      1      |       1
19    |    Pluf    |    Tom    |    rose    |    4    |      1      |       0

**tatayo** · 04/12/2016, 08h24

En relisant la demande, je pense que cette requête peut faire le boulot:

Code sql :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT MyTable.MyTableID as ID,
       MyTable.AAA as Pseudo,
       Common.Name as Nom,
       Common.Version as Version,
       Common.Color as Couleur
FROM MyTable
JOIN Common ON MyTable.FKCommonID = Common.CommonID
left outer join common as c2 on c2.version > common.version
where c2.CommonId is null

Tatayo.

**Feneck91** · 04/12/2016, 11h08

Avec ta requête, le résultat ne comporte plus qu'une seule ligne...

**vttman** · 04/12/2016, 11h33

Je propose

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT MyTable.MyTableID as ID,
          MyTable.AAA as Pseudo,
          Common.Name as Nom,
          commonmax.maxv,
         Common.Color as Couleur
FROM MyTable 
 inner JOIN Common 
ON MyTable.FKCommonID = Common.CommonID
 inner join (select name as name, max(version) as maxv from common group by name) as commonmax
on Common.Name = commonmax.name
order by 1

jeu essai 1
Nom : Capture.JPG
Affichages : 234
Taille : 18,7 Ko

Jeu essai 2
Nom : Capture.JPG
Affichages : 259
Taille : 21,3 Ko

**Feneck91** · 04/12/2016, 12h09

Tes images ne fonctionnent pas, dommage...
Par contre, ta requête fonctionne bien.

Les deux requêtes suivantes donnent le même résultat (correct).
Deux questions :

J'ai retiré le ORDER BY 1, il sert à quoi ?
Cette requête est plus optimisée que la mienne ?

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
SELECT MyTable.MyTableID as ID,
       MyTable.AAA as Pseudo,
       Common.Name as Nom,
       Common.Color as Couleur,
       Common.Version as Version,
       (SELECT MaxVersion FROM
           (SELECT * FROM (SELECT Name, MAX(Version) as MaxVersion
                           FROM Common
                           GROUP BY Name
                          )
            WHERE Name = Common.Name
           )
        ),
       (SELECT MaxVersion FROM
           (SELECT * FROM (SELECT Name, MAX(Version) as MaxVersion
                           FROM Common
                           GROUP BY Name
                          )
            WHERE Name = Common.Name
           )
        ) = Common.Version
FROM MyTable
JOIN Common
ON MyTable.FKCommonID = Common.CommonID;
 
 
SELECT MyTable.MyTableID as ID,
          MyTable.AAA as Pseudo,
          Common.Name as Nom,
          Common.Color as Couleur,
          Common.Version,
          commonmax.maxv,
          commonmax.maxv = Common.Version
FROM MyTable 
 inner JOIN Common 
ON MyTable.FKCommonID = Common.CommonID
 INNER JOIN (SELECT name as name, max(version) as maxv from common group by name) as commonmax
ON Common.Name = commonmax.name;

**vttman** · 04/12/2016, 12h12

le

Code :

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

order by 1

il trie les résultats sur le 1er champ indiqué dans la requête

ex :

Code :

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

 ... order by 1 asc, 3 desc, 2 asc etc.

**vttman** · 04/12/2016, 12h14

Plus optimisée je ne sais pas ...
mais plus courte !
et sans doute facile à maintenir / comprendre ?

**Feneck91** · 04/12/2016, 12h42

Je te confirme, elle est bien ta requête... En plus si on utilise plus d'une fois le résultat maxv, il n'y a pas besoin de recopier plusieurs fois la requête.
Dans ce cas, le ORDER BY ne sert à rien (ou je me trompe) ? Les résultats sont corrects avec ou sans.

**vttman** · 04/12/2016, 13h02

oui le

Code :

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

order by ...

ne change rien aux lignes de résultats ...
il se charge juste de les ordonner ici.

**Feneck91** · 04/12/2016, 14h03

Ok, donc comme c'est pour la création d'une vue, cela ne me servira à rien...
Je vais retenir ta requête, une pierre angulaire la fonctionnalité de filtrer sur 'dernière version uniquement' super importante pour le programme que je code depuis un 18 mois dans ma boite...

Depuis le début, on arrête pas de me dire : met un champ "dernière version" si la requête est trop complexe (rappel, je l'ai déjà codé depuis des mois en LinQ + Entity Framework) et je m'y refuse, à gérer ça va être le bordel, une base de données sert à faire ce genre de calcul et puis le risque de bug où on va se retrouver avec deux éléments de même nom de version différentes taggué dernière version ou alors pas de dernière version du tout... Non, il faut être raisonnable, c'est une bien mauvaise idée de créer ce champ, il vaut mieux le calculer...

Jointure : SELECT dans SELECT + GROUP : déterminez si dernière version

Langage SQL

Discussions similaires

Partager

Partager