Utiliser des sous-requêtes dans la clause FROM pour doubler les agrégats▲
Les sous-requêtes dans la clause FROM sont parfois mentionnées comme des tables virtuelles ou dérivées. Elles représentent un jeu d'informations, dans son ensemble, qui peut être joint et sélectionné depuis une table normale. Pour faire une sous-requête dans la clause FROM, entourez simplement l'expression SELECT avec des parenthèses et donnez un alias ou un nom pour la table dérivée.
Retournons à notre exemple, ceci est le jeu d'informations que nous voulons éventuellement joindre à notre rand de prédicat.
SELECT
MONTHNAME(created) AS Month
, COUNT(*) AS Added
FROM feeds
WHERE created >= '2007-01-01'
GROUP BY MONTH(created);Quoi qu'il en soit, comme constaté précédemment, puisque notre SELECT contient déjà un GROUP BY, nous devons utiliser une table dérivée pour générer une somme courante. Pour faire ceci, nous allons construire la commande SQL complexe par étapes.
D'abord, entourons notre expression SELECT avec des parenthèses et donnons-lui un alias.
(
SELECT
MONTH(created) AS MonthNo
, MONTHNAME(created) AS MonthName
, COUNT(*) AS Added
FROM feeds
WHERE created >= '2007-01-01'
GROUP BY MONTH(created)
) AS x1Ensuite, en suivant la formule générale donnée dans la commande 2, nous allons créer un second jeu identique pour joindre au premier jeu. Donc nous utilisons une autre table dérivée et nous lui donnons un alias x2 cette fois :
(
SELECT
MONTH(created) AS MonthNo
, MONTHNAME(created) AS MonthName
, COUNT(*) AS Added
FROM feeds
WHERE created >= '2007-01-01'
GROUP BY MONTH(created)
) AS x2Ensuite nous voulons appliquer la condition de jointure, pour ce faire, bloquons une expression SELECT extérieure, et une clause FROM contant les deux tables dérivées et une condition de jointure.
En pseudocode, quelque chose comme ceci :
SELECT ...
FROM
( x1 SELECT... ) AS x1
INNER JOIN ( x2 SELECT ) AS x2
ON x1.key >= x2.key
GROUP BY x1.key;En écrivant le SQL en pseudocode comme dans la commande 6, vous vous faites une meilleure idée de comment les pièces fonctionnent entre elles, sans s'enliser dans une expression SQL plus longue, plus complexe.
Les seules choses qu'il reste à faire à partir de maintenant sont :
- inclure les champs SELECT dans le SELECT extérieur et remplacer « key » par le vrai nom de champs ;
- remplacer le pseudocode utilisé pour la table dérivée de l'expression SELECT par le vrai code SQL.
Dans l'ordre, allons-y.
D'abord incluant les champs SELECT dans l'expression SELECT extérieure.
SELECT
x1.MonthNo
, x1.MonthName
, x1.Added
, SUM(x2.Added) AS RunningTotal
FROM
( x1 SELECT... ) AS x1
INNER JOIN ( x2 SELECT ) AS x2
ON x1.MonthNo >= x2.MonthNo
GROUP BY x1.MonthNo;L'expression extérieure SELECT doit contenir le champ clé depuis x1 (MonthNo), le champ Added depuis x1 (qui contient le nombre de commentaires par mois), et la somme des jeux de valeurs de Added depuis x2, qui contient le nombre de commentaires pour tous les mois précédents et incluant le mois en cours dans x1.
De plus, j'ai aussi mis une dépendance (sur le champ onMonth), MonthName , depuis x1 dans un but décoratif.
L'étape finale est de remplacer la table dérivée par le code SQL actuel.
SELECT
x1.MonthNo
, x1.MonthName
, x1.Added
, SUM(x2.Added) AS RunningTotal
FROM
(
SELECT
MONTH(created) AS MonthNo
, MONTHNAME(created) AS MonthName
, COUNT(*) AS Added
FROM feeds
WHERE created >= '2007-01-01'
GROUP BY MONTH(created)
) AS x1
INNER JOIN (
SELECT
MONTH(created) AS MonthNo
, MONTHNAME(created) AS MonthName
, COUNT(*) AS Added
FROM feeds
WHERE created >= '2007-01-01'
GROUP BY MONTH(created)
) AS x2
ON x1.MonthNo >= x2.MonthNo
GROUP BY x1.MonthNo;Voila , maintenant nous avons une somme courante.
+---------+-----------+-------+--------------+
| MonthNo | MonthName | Added | RunningTotal |
+---------+-----------+-------+--------------+
| 1 | January | 1 | 1 |
| 2 | February | 1 | 2 |
| 3 | March | 11 | 13 |
| 4 | April | 8 | 21 |
| 5 | May | 18 | 39 |
| 6 | June | 3 | 42 |
+---------+-----------+-------+--------------+
6 rows in set (0.00 sec)Il y a juste un petit problème dans la requête, elle ne montre pas en réalité le nombre total de commentaires sur PlanetMySQL, seulement ceux ajoutés en 2007.
Et si nous voulions afficher le nombre total de commentaires ? Facile, nous pouvons utiliser une variable utilisateur contenant le nombre de commentaires non ajoutés en 2007 et l'ajouter au total courant.
Voici la commande SQL finale et son résultat. En espérant que cela aidera d'autres personnes.
SELECT COUNT(*) INTO @2006total FROM feeds WHERE created < '2007-01-01';
SELECT
x1.MonthNo
, x1.MonthName
, x1.Added
, SUM(x2.Added) + @2006total AS RunningTotal
FROM
(
SELECT
MONTH(created) AS MonthNo
, MONTHNAME(created) AS MonthName
, COUNT(*) AS Added
FROM feeds
WHERE created >= '2007-01-01'
GROUP BY MONTH(created)
) AS x1
INNER JOIN (
SELECT
MONTH(created) AS MonthNo
, MONTHNAME(created) AS MonthName
, COUNT(*) AS Added
FROM feeds
WHERE created >= '2007-01-01'
GROUP BY MONTH(created)
) AS x2
ON x1.MonthNo >= x2.MonthNo
GROUP BY x1.MonthNo;mysql> SELECT @2006total;
+------------+
| @2006total |
+------------+
| 159 |
+------------+
1 row in set (0.00 sec)
<snip>
+---------+-----------+-------+--------------+
| MonthNo | MonthName | Added | RunningTotal |
+---------+-----------+-------+--------------+
| 1 | January | 1 | 160 |
| 2 | February | 1 | 161 |
| 3 | March | 11 | 172 |
| 4 | April | 8 | 180 |
| 5 | May | 18 | 198 |
| 6 | June | 3 | 201 |
+---------+-----------+-------+--------------+
6 rows in set (0.00 sec)