Commentaires

Bonjour Lyche,

très bon article, très clair et efficace, et surtout des exemples concrets et réalistes, pas comme quand dans les documentations des éditeurs qui donnent des exemples totalement dénués de sens :-)

Je viens de mettre en pratique sur Informix V14.10 et tout cela marche très bien!

N'ayant pas encore regardé les autres articles, j'avais une petite question:
comment peut on utiliser les CTE avec des curseurs dans une application développée avec un vrai langage?
La CTE marche très bien quand on exécute les CTE dans un éditeur SQL, mais je ne vois pas trop comment on peut, et surtout dans le cas où l'on va exécuter plusieurs requêtes sur la CTE, déclarer le curseur et envoyer le résultat dans une une variable ou structure ?

Envoyé par ericung

Bonjour
Je vous remercie pour m'avoir fait découvrir les CTE !
Les sous requêtes peuvent aussi être utilisé dans le SELECT.
La seul condition est que ces sous tables ne retourne qu'un résultat max.
C'est très utile dans les agrégats
Exemple :
SELECT E.Nom
, (SELECT ee FROM TableSousRequete1 WHERE e = E.e) AS ee
, (SELECT ff FROM TableSousRequete2 WHERE f = E.f) AS ff
, (SELECT gg FROM TableSousRequete3 WHERE g = E.g) AS gg
FROM TB_Employe AS E
WHERE E.d = 1

Cela permet d'écrire une requête plus simple à lire.

Bonjour,

Attention à ne pas abuser de sous requête dans le SELECT, les performances peuvent en être affectée.
Mais oui, cela fonctionne aussi de cette façon

Cordialement,

Bonjour
Je vous remercie pour m'avoir fait découvrir les CTE !
Les sous requêtes peuvent aussi être utilisé dans le SELECT.
La seul condition est que ces sous tables ne retourne qu'un résultat max.
C'est très utile dans les agrégats
Exemple :
SELECT E.Nom
, (SELECT ee FROM TableSousRequete1 WHERE e = E.e) AS ee
, (SELECT ff FROM TableSousRequete2 WHERE f = E.f) AS ff
, (SELECT gg FROM TableSousRequete3 WHERE g = E.g) AS gg
FROM TB_Employe AS E
WHERE E.d = 1

Cela permet d'écrire une requête plus simple à lire.

Envoyé par Wachter

Bonjour,

Serait-il possible d'utiliser une CTE (pour remplacer une sous-requête) dans une clause WHERE ?

Code sql :

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

1
2
3
4
5
6
7
8
9
10
WITH cte AS
(
  SELECT co.ClientId
  FROM Commande
)

SELECT cl.ClientId
FROM Client cl
WHERE cl.ClientId IN (cte)

Avec beaucoup de retard suite à divers soucis.
Oui, tout à fait, il faut simplement considérer la CTE comme une table a usage unique. Tu peux même faire des UPDATE de ta CTE qui impacterons la table. A condition que la requête n'affecte qu'une seule table.

Cordialement,

Bonjour,

Serait-il possible d'utiliser une CTE (pour remplacer une sous-requête) dans une clause WHERE ?

Code sql :

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

1
2
3
4
5
6
7
8
9
10
WITH cte AS
(
  SELECT co.ClientId
  FROM Commande
)

SELECT cl.ClientId
FROM Client cl
WHERE cl.ClientId IN (cte)

Envoyé par alassanediakite

Salut
Pouvez-vous détailler plus ...

... ou plutôt donner des références de cette affirmation.
Du courage pour d'autres articles
@+

Bonjour,

Une référence fiable peut être trouvée ici http://www.programmerinterview.com/i...e-vs-subquery/

J'ai probablement été un peu "sec" dans mon propos et je corrigerais ça afin de paraitre moins catégorique si cela gène . Néanmoins, je vais faire quelques recherches afin de confirmer ou infirmer cette question.

Cordialement,

Salut
Pouvez-vous détailler plus ...

Les tables dérivées sont souvent, à tort, appelées « sous-requêtes », une requête ne pouvant être appelée sous-requête que lorsqu’elle est située dans une clause Where.

... ou plutôt donner des références de cette affirmation.
Du courage pour d'autres articles
@+

Envoyé par julien94320

Merci monsieur pour cet article très bien réalisé

Julien

Merci à toi pour l'encouragement

Merci monsieur pour cet article très bien réalisé

Julien

Envoyé par Jitou

C'est un peu comme l'utilisation d'une vue sauf que l'on ne garde pas la sous requête en mémoire ? Sinon article très bien écrit et facile à comprendre.

Il n'y a en effet aucune persistance du code au niveau du SGBD comme une vue pourrait le faire. Cependant, les plans d'exécution et la mise en cache des données extraite s'applique comme lors de l'utilisation d'une vue.

C'est un peu comme l'utilisation d'une vue sauf que l'on ne garde pas la sous requête en mémoire ? Sinon article très bien écrit et facile à comprendre.

très intéressant, merci pour ton retour

Pour PostgreSQL il faut la version 9.4 minimum.
Je viens de faire des essais et avec quelques modification syntaxique on arrive à un résultats (différents dans les montants, mais je suppose, qu'on utilise pas les même valeurs ).

Voici ce que j'ai modifié pour faire fonctionner la dernière requête dans PostgreSQL 9.5

Create table et Insertion :

Code sql :

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
 
 
CREATE TABLE tb_departements ( 
  id_departement  INT PRIMARY KEY 
, nom_departement VARCHAR( 50 ) ); 
 
 
CREATE TABLE tb_employes ( 
  id_employe      INT PRIMARY KEY 
, nom_employe     VARCHAR( 50 ) NOT NULL 
, age_employe     INT 
, departement_id  INT 
, salaire_employe MONEY ); 
 
 
INSERT INTO tb_departements 
VALUES( 1, 'Administration'       ); 
INSERT INTO tb_departements 
VALUES( 2, 'Ressources Humaines'  ); 
INSERT INTO tb_departements 
VALUES( 3, 'Service Informatique' ); 
INSERT INTO tb_departements 
VALUES( 4, 'Comptabilité'         ); 
 
INSERT INTO tb_employes 
VALUES( 1, 'Georges', 74, 4, 2480.3 ); 
INSERT INTO tb_employes 
VALUES( 2, 'Pierre' , 17, 3, 1387.2 ); 
INSERT INTO tb_employes 
VALUES( 3, 'Bernard', 63, 1, 3499.8 ); 
INSERT INTO tb_employes 
VALUES( 4, 'John'   , 23, 3, 1876.9 ); 
INSERT INTO tb_employes 
VALUES( 5, 'Jérome' , 45, 2, 2286.6 ); 
INSERT INTO tb_employes 
VALUES( 6, 'Lina', 30, 3, 2230.4 ); 
INSERT INTO tb_employes 
VALUES( 7, 'Marie', 26, 3, 1980.4 ); 
INSERT INTO tb_employes 
VALUES( 8, 'Virginie', 37, 3, 2730.4 ); 
INSERT INTO tb_employes 
VALUES( 9, 'Hélène', 33, 3, 2430.4 );
 
 
 
CREATE TABLE intervention_client ( 
  id_intervention   INT PRIMARY KEY 
, date_intervention Date 
, nom_client        VARCHAR( 50 ) 
, employe_id        INT 
, facturation       Money ) 
 
 
INSERT INTO intervention_client 
VALUES(  1, '2014-01-02', 'Client 1', 2, 500 ); 
INSERT INTO intervention_client 
VALUES(  2, '2014-01-03', 'Client 1', 2, 650 ); 
INSERT INTO intervention_client 
VALUES(  3, '2014-01-06', 'Client 1', 2, 650 ); 
INSERT INTO intervention_client 
VALUES(  4, '2014-01-07', 'Client 1', 2, 650 ); 
INSERT INTO intervention_client 
VALUES(  5, '2014-01-08', 'Client 1', 2, 650 ); 
INSERT INTO intervention_client 
VALUES(  6, '2014-01-09', 'Client 1', 2, 650 ); 
INSERT INTO intervention_client 
VALUES(  7, '2014-01-10', 'Client 1', 2, 650 ); 
INSERT INTO intervention_client 
VALUES(  8, '2014-01-03', 'Client 1', 4, 400 ); 
INSERT INTO intervention_client 
VALUES(  9, '2014-01-04', 'Client 1', 4, 650 ); 
INSERT INTO intervention_client 
VALUES( 10, '2014-01-06', 'Client 1', 4, 650 ); 
INSERT INTO intervention_client 
VALUES( 11, '2014-01-07', 'Client 1', 4, 650 ); 
INSERT INTO intervention_client 
VALUES( 12, '2014-01-08', 'Client 1', 4, 650 ); 
INSERT INTO intervention_client 
VALUES( 13, '2014-01-03', 'Client 2', 6, 650 ); 
INSERT INTO intervention_client 
VALUES( 14, '2014-01-04', 'Client 2', 6, 650 ); 
INSERT INTO intervention_client 
VALUES( 15, '2014-01-06', 'Client 2', 6, 650 ); 
INSERT INTO intervention_client 
VALUES( 16, '2014-01-07', 'Client 2', 6, 650 ); 
INSERT INTO intervention_client 
VALUES( 17, '2014-01-08', 'Client 2', 6, 650 ); 
INSERT INTO intervention_client 
VALUES( 18, '2014-01-09', 'Client 2', 6, 650 ); 
INSERT INTO intervention_client 
VALUES( 19, '2014-01-10', 'Client 2', 6, 650 ); 
INSERT INTO intervention_client 
VALUES( 20, '2014-01-06', 'Client 3', 7, 650 ); 
INSERT INTO intervention_client 
VALUES( 21, '2014-01-07', 'Client 3', 7, 650 ); 
INSERT INTO intervention_client 
VALUES( 22, '2014-01-08', 'Client 3', 7, 650 ); 
INSERT INTO intervention_client 
VALUES( 23, '2014-01-09', 'Client 3', 7, 650 ); 
INSERT INTO intervention_client 
VALUES( 24, '2014-01-10', 'Client 3', 7, 650 ); 
INSERT INTO intervention_client 
VALUES( 25, '2014-01-06', 'Client 3', 8, 700 ); 
INSERT INTO intervention_client 
VALUES( 26, '2014-01-07', 'Client 3', 8, 700 ); 
INSERT INTO intervention_client 
VALUES( 27, '2014-01-08', 'Client 3', 8, 700 ); 
INSERT INTO intervention_client 
VALUES( 28, '2014-01-09', 'Client 3', 8, 700 ); 
INSERT INTO intervention_client 
VALUES( 29, '2014-01-10', 'Client 3', 8, 700 ); 
INSERT INTO intervention_client 
VALUES( 30, '2014-01-03', 'Client 4', 9, 650 ); 
INSERT INTO intervention_client 
VALUES( 31, '2014-01-06', 'Client 4', 9, 650 ); 
INSERT INTO intervention_client 
VALUES( 32, '2014-01-07', 'Client 4', 9, 650 ); 
INSERT INTO intervention_client 
VALUES( 33, '2014-01-08', 'Client 4', 9, 650 ); 
INSERT INTO intervention_client 
VALUES( 34, '2014-01-09', 'Client 4', 9, 650 ); 
INSERT INTO intervention_client 
VALUES( 35, '2014-01-10', 'Client 4', 9, 650 );
 
 
INSERT INTO intervention_client 
VALUES( 36, '2014-02-03', 'Client 1', 2, 750 ); 
INSERT INTO intervention_client 
VALUES( 37, '2014-02-04', 'Client 1', 2, 500 ); 
INSERT INTO intervention_client 
VALUES( 38, '2014-02-05', 'Client 1', 2, 750 ); 
INSERT INTO intervention_client 
VALUES( 39, '2014-02-06', 'Client 1', 2, 750 ); 
INSERT INTO intervention_client 
VALUES( 40, '2014-02-07', 'Client 1', 2, 750 ); 
INSERT INTO intervention_client 
VALUES( 41, '2014-02-10', 'Client 1', 2, 750 ); 
INSERT INTO intervention_client 
VALUES( 42, '2014-02-11', 'Client 1', 2, 750 ); 
INSERT INTO intervention_client 
VALUES( 43, '2014-02-12', 'Client 1', 2, 750 ); 
INSERT INTO intervention_client 
VALUES( 44, '2014-02-13', 'Client 1', 2, 750 ); 
INSERT INTO intervention_client 
VALUES( 45, '2014-02-14', 'Client 1', 2, 750 ); 
INSERT INTO intervention_client 
VALUES( 46, '2014-02-03', 'Client 1', 4, 400 ); 
INSERT INTO intervention_client 
VALUES( 47, '2014-02-04', 'Client 1', 4, 750 ); 
INSERT INTO intervention_client 
VALUES( 48, '2014-02-05', 'Client 1', 4, 750 ); 
INSERT INTO intervention_client 
VALUES( 49, '2014-02-06', 'Client 1', 4, 750 ); 
INSERT INTO intervention_client 
VALUES( 50, '2014-02-07', 'Client 1', 4, 750 ); 
INSERT INTO intervention_client 
VALUES( 51, '2014-02-03', 'Client 2', 6, 750 ); 
INSERT INTO intervention_client 
VALUES( 52, '2014-02-04', 'Client 2', 6, 750 ); 
INSERT INTO intervention_client 
VALUES( 53, '2014-02-05', 'Client 2', 6, 750 ); 
INSERT INTO intervention_client 
VALUES( 54, '2014-02-06', 'Client 2', 6, 750 ); 
INSERT INTO intervention_client 
VALUES( 55, '2014-02-07', 'Client 2', 6, 750 ); 
INSERT INTO intervention_client 
VALUES( 56, '2014-02-10', 'Client 2', 6, 750 ); 
INSERT INTO intervention_client 
VALUES( 57, '2014-02-19', 'Client 2', 6, 750 ); 
INSERT INTO intervention_client 
VALUES( 58, '2014-02-06', 'Client 3', 7, 750 ); 
INSERT INTO intervention_client 
VALUES( 59, '2014-02-07', 'Client 3', 7, 750 ); 
INSERT INTO intervention_client 
VALUES( 60, '2014-02-10', 'Client 3', 7, 750 ); 
INSERT INTO intervention_client 
VALUES( 61, '2014-02-11', 'Client 3', 7, 750 ); 
INSERT INTO intervention_client 
VALUES( 62, '2014-02-12', 'Client 3', 7, 750 ); 
INSERT INTO intervention_client 
VALUES( 63, '2014-02-06', 'Client 3', 8, 700 ); 
INSERT INTO intervention_client 
VALUES( 64, '2014-02-07', 'Client 3', 8, 700 ); 
INSERT INTO intervention_client 
VALUES( 65, '2014-02-10', 'Client 3', 8, 700 ); 
INSERT INTO intervention_client 
VALUES( 66, '2014-02-11', 'Client 3', 8, 700 ); 
INSERT INTO intervention_client 
VALUES( 67, '2014-02-12', 'Client 3', 8, 700 ); 
INSERT INTO intervention_client 
VALUES( 68, '2014-02-03', 'Client 4', 9, 750 ); 
INSERT INTO intervention_client 
VALUES( 69, '2014-02-06', 'Client 4', 9, 750 ); 
INSERT INTO intervention_client 
VALUES( 70, '2014-02-07', 'Client 4', 9, 750 ); 
INSERT INTO intervention_client 
VALUES( 71, '2014-02-10', 'Client 4', 9, 750 ); 
INSERT INTO intervention_client 
VALUES( 72, '2014-02-11', 'Client 4', 9, 750 ); 
INSERT INTO intervention_client 
VALUES( 73, '2014-02-12', 'Client 4', 9, 750 );

Requêtes :

Code sql :

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

1
2
3
4
5
6
7
8
9
 
SELECT COALESCE( nom_employe, 'Total Annuel' ) AS nom_employe 
     , CASE WHEN nom_employe IS NULL AND EXTRACT(MONTH FROM date_intervention ) IS NULL 
            THEN NULL 
            ELSE COALESCE( CAST( EXTRACT(MONTH FROM date_intervention ) AS VARCHAR ), 'Total Mensuel' ) END AS "MOIS_INTERVENTION" 
     , SUM( facturation )         AS "SOMME_FACTUREE" 
  FROM tb_employes e 
       INNER JOIN intervention_client i ON e.id_employe = i.employe_id 
 GROUP BY ROLLUP (nom_employe, EXTRACT(MONTH FROM date_intervention ));

Envoyé par WebPac

Salut,

cet article est très instructif.
Par contre, est-ce que la clause GROUP BY ROLL UP est supportée par beaucoup de base de données ?
Je présume que c'est le cas de SQL Server et Oracle, mais est-ce le cas pour des bases telles que MySQL, PostgreSQL ou Firebird ?

Merci.

Bonjour,

Très bonne question en effet. Il se trouve que MySQL et PGSQL implément tout ou partie de ces fonctionnalités Firebird lui n'en a implémenté aucune.

MySQL a implémenté un ROLLUP à sa façon en précisant la clause WITH ROLLUP après la déclaration du groupement
Exemple:

Code sql :

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

1
2
 
GROUP BY A, B, C WITH ROLLUP

PG SQL a quant-à lui implémenté les 3 groupements GROUPING SET, CUBE, ROLLUP, l'écriture est conforme à la norme SQL
Il est a noter que Sybase aussi a implémenté les 3 fonctionnalités.

Cordialement,

Vraiment sympa comme article, merci Lyche.

Salut,

cet article est très instructif.
Par contre, est-ce que la clause GROUP BY ROLL UP est supportée par beaucoup de base de données ?
Je présume que c'est le cas de SQL Server et Oracle, mais est-ce le cas pour des bases telles que MySQL, PostgreSQL ou Firebird ?

Merci.

Envoyé par jimbolion

Merci encore pour cet article qui éclaire de façon constructive l'utilisation des sous requêtes et CTE

Merci a toi pour l'aide

Envoyé par juvamine

Commentaire pas très constructif, mais c'était pour faire un salut amical à Lyche

Joli petit tuto !

Merci poto ^_^

Franchement sympa comme article

Commentaire pas très constructif, mais c'était pour faire un salut amical à Lyche

Joli petit tuto !