SQL - utilisation d'alias dans le groupe par
juste curieux à propos de la syntaxe SQL. Donc si j'ai
SELECT
itemName as ItemName,
substring(itemName, 1,1) as FirstLetter,
Count(itemName)
FROM table1
GROUP BY itemName, FirstLetter
ce serait incorrect parce que
GROUP BY itemName, FirstLetter
devrait vraiment être
GROUP BY itemName, substring(itemName, 1,1)
mais pourquoi ne pouvons-nous pas simplement utiliser le premier pour la commodité?
9 réponses
SQL est implémenté comme si une requête était exécutée dans l'ordre suivant:
- de la clause
- WHERE clause
- GROUP BY clause
- avec clause
- SELECT clause
- ORDER BY clause
pour la plupart des systèmes de bases de données relationnelles, cet ordre explique quels noms (colonnes ou alias) sont valides parce qu'ils doivent ont été introduits dans l'étape précédente.
ainsi dans Oracle et SQL Server, vous ne pouvez pas utiliser un terme dans le groupe par clause que vous définissez dans la clause SELECT parce que le groupe par est exécuté avant la clause SELECT.
il y a cependant des exceptions: MySQL et Postgres semblent avoir une intelligence supplémentaire qui le permet.
vous pouvez toujours utiliser une sous-commande pour pouvoir utiliser l'alias; bien sûr, vérifiez les performances (il est Possible que le serveur db exécute les deux de la même façon, mais cela ne fait jamais de mal de vérifier):
SELECT ItemName, FirstLetter, COUNT(ItemName)
FROM (
SELECT ItemName, SUBSTRING(ItemName, 1, 1) AS FirstLetter
FROM table1
) ItemNames
GROUP BY ItemName, FirstLetter
au moins dans PostgreSQL vous pouvez utiliser le numéro de colonne dans le groupe de résultats par clause:
SELECT
itemName as ItemName,
substring(itemName, 1,1) as FirstLetter,
Count(itemName)
FROM table1
GROUP BY 1, 2
bien sûr, cela commence à être une douleur si vous faites cela de manière interactive et que vous éditez la requête pour changer le nombre ou l'ordre des colonnes dans le résultat. Mais tout de même.
SQL Server ne vous permet pas de faire référence à l'alias dans le groupe par clause en raison de l'ordre logique du traitement. Le groupe par clause est traité avant la clause SELECT, de sorte que l'alias n'est pas connu lorsque le groupe par clause est évalué. Ceci explique aussi pourquoi vous pouvez utiliser l'alias dans l'ordre Par clause.
Voici une source d'information sur la SQL Server logical processing phases .
attention que l'utilisation d'alias dans le groupe par (pour les services qui le prennent en charge, tels que postgres) peut avoir des résultats inattendus. Par exemple, si vous créez un alias qui existe déjà dans la déclaration intérieure, le groupe par choisira le nom du champ intérieur.
-- Working example in postgres
select col1 as col1_1, avg(col3) as col2_1
from
(select gender as col1, maritalstatus as col2,
yearlyincome as col3 from customer) as layer_1
group by col1_1;
-- Failing example in postgres
select col2 as col1, avg(col3)
from
(select gender as col1, maritalstatus as col2,
yearlyincome as col3 from customer) as layer_1
group by col1;
certains DBMSs vous permettront d'utiliser un alias au lieu de devoir répéter l'expression entière.
Teradata est un exemple.
je éviter la position ordinale de notation tel que recommandé par le projet de Loi pour les raisons expliquées dans cela DONC, la question .
l'alternative facile et robuste est de toujours répéter l'expression dans le groupe par clause.
DRY ne s'applique pas à SQL.
méfiez-vous de l'utilisation d'alias lors du regroupement des résultats à partir d'une vue en SQLite. Vous obtiendrez des résultats inattendus si le nom d'alias est le même que le nom de colonne de n'importe quelle table sous-jacente (aux vues.)
le jour où j'ai découvert que Rdb, l'ancien produit DEC maintenant supporté par Oracle permettait à la colonne alias d'être utilisé dans le groupe par. Mainstream Oracle par la version 11 ne permet pas que la colonne alias soit utilisée dans le groupe par. Pas sûr de ce que Postgresql, SQL Server, MySQL, etc permettra ou ne permettra pas. YMMV.
Je ne réponds pas pourquoi il en est ainsi, mais je voulais seulement montrer un moyen de contourner cette limitation dans SQL Server en utilisant CROSS APPLY
pour créer l'alias. Vous l'utilisez alors dans la clause GROUP BY
, comme ceci:
SELECT
itemName as ItemName,
FirstLetter,
Count(itemName)
FROM table1
CROSS APPLY (SELECT substring(itemName, 1,1) as FirstLetter) Alias
GROUP BY itemName, FirstLetter