introduit dans SQL Server 2005, l’expression de table commune (CTE) est un ensemble de résultats nommé temporaire que vous pouvez référencer dans une instruction SELECT, INSERT, UPDATE ou DELETE. Vous pouvez également utiliser un CTE dans une instruction CREATE VIEW, dans le cadre de la requête SELECT de la vue. En outre, à partir de SQL Server 2008, vous pouvez ajouter un CTE à la nouvelle instruction MERGE.
SQL Server prend en charge deux types de CTEs: récursif et non récursif. Dans cet article, j’explique comment créer deux types., Les exemples que je fournis sont basés sur une instance locale de SQL Server 2008 et récupèrent des données de L’exemple de base de données AdventureWorks2008.
travailler avec des Expressions de Table communes
vous définissez CTEs en ajoutant une clause WITH directement avant votre instruction SELECT, INSERT, UPDATE, DELETE ou MERGE., »>
|
1
2
3
4
5
|
]
<common_table_expression>::=
cte_name )]
AS (cte_query)
|
…which can be represented like this…
As you can see, if you include more than one CTE in your WITH clause, you must separate them with commas., En outre, pour chaque CTE, vous devez fournir un nom, le mot clé AS et une instruction SELECT. Vous pouvez également fournir des noms de colonnes (séparés par des virgules), tant que le nombre de noms correspond au nombre de colonnes renvoyées par le jeu de résultats.
L’instruction SELECT de votre requête CTE doit suivre les mêmes exigences que celles utilisées pour créer une vue. Pour plus de détails sur ces exigences, consultez la rubrique « Créer une vue (Transact-SQL)” dans SQL Server Books Online. Pour plus de détails sur CTEs en général, voir la rubrique « avec common_table_expression (Transact-SQL)., »
Après avoir défini votre clause WITH avec les CTEs nécessaires, vous pouvez alors référencer ces CTEs comme vous le feriez pour toute autre table. Cependant, vous ne pouvez référencer un CTE que dans la portée d’exécution de l’instruction qui suit immédiatement la clause WITH. Après avoir exécuté votre Instruction, Le jeu de résultats CTE n’est pas disponible pour les autres instructions.
création d’une Expression de Table commune non récursive
un CTE non récursif est un CTE qui ne se référence pas dans le CTE. Les CTEs Non récursifs ont tendance à être plus simples que les ctes récursifs, c’est pourquoi je commence par ce type.,pour ce faire, vous pouvez utiliser la fonction de partage de données (« div id= »2b9f089d6c ») et la fonction de partage de données (« div id= »2e8633d68c »).>
Après avoir spécifié le nom CTE, je fournis deux noms de colonne, SalesPersonID et NetSales, qui sont entre parenthèses et séparés par une virgule. Cela signifie que le jeu de résultats renvoyé par la requête CTE doit renvoyer deux colonnes.
ensuite, je fournis le mot-clé AS, puis un ensemble de parenthèses qui entourent la requête CTE. Dans ce cas, L’instruction SELECT renvoie le total des ventes pour chaque vendeur (total des ventes groupées par ID de vendeur)., Comme vous pouvez le voir, la requête CTE peut inclure des fonctions Transact-SQL, des clauses GROUP BY ou tout élément que L’instruction SELECT dans une définition de vue peut inclure.
je peux maintenant faire référence à cteTotalSales dans la déclaration qui suit immédiatement. Pour cet exemple, je crée une instruction SELECT qui joint les ventes.vue vSalesPerson vers cteTotalSales, basée sur l’ID du vendeur. Je tire ensuite les noms et les emplacements de la vue et les ventes nettes du CTE. Le tableau suivant montre les résultats renvoyés par cette instruction.,
Comme vous l’avez vu plus tôt dans la syntaxe, vous pouvez inclure plusieurs expressions de table communes dans une clause.,
le premier CTE-cteTotalSales-est similaire à celui de l’exemple précédent, sauf que la clause WHERE a été en outre qualifiée pour inclure les ventes uniquement à partir de 2003. Après avoir défini cteTotalSales, j’ajoute une virgule, Puis je définis cteTargetDiff, qui calcule la différence entre le total des ventes et le quota de ventes.
la nouvelle définition CTE spécifie trois colonnes pour le jeu de résultats: SalesPersonID, SalesQuota et QuotaDiff., Comme vous pouvez vous y attendre, la requête CTE renvoie trois colonnes. Le premier est l’ID du vendeur. Le second est le quota de vente. Cependant, comme un quota de vente n’est pas défini pour certains vendeurs, j’utilise une instruction CASE. Si la valeur est null, cette valeur est définie sur 0, sinon la valeur SalesQuota réelle est utilisée.
la dernière colonne renvoyée est la différence entre les ventes nettes et le quota de ventes. Encore une fois, j’utilise une déclaration de cas. Si la valeur SalesQuota est nulle, la valeur NetSales est utilisée, sinon le quota de ventes est soustrait des ventes nettes pour obtenir la différence.,
quelque chose d’intéressant à noter à propos de la deuxième requête CTE est que j’ai rejoint les ventes.Tableau des vendeurs au premier CTE-cteTotalSales-afin que je puisse calculer la différence entre les ventes totales et le quota de vente. Chaque fois que vous définissez plusieurs CTEs dans une seule clause WITH, vous pouvez faire référence aux CTEs précédents (mais pas l’inverse).
Une fois que j’ai défini mes CTEs, je peux les référencer dans la première instruction qui suit le CTE, comme vous l’avez vu dans l’exemple précédent. Dans ce cas, je rejoins les ventes.,vsalesperson afficher à cteTotalSales, puis se joindre à cteTargetDiff, tous basés sur l’ID du vendeur. Ma liste de sélection comprend ensuite des colonnes provenant des trois sources. L’instruction renvoie les résultats affichés dans le tableau suivant.
comme vous pouvez le voir, les données sur les ventes sont fournies pour tous les vendeurs, y compris la ville dans laquelle ils résident, leurs ventes nettes, leur quota de ventes et la différence calculée entre les deux chiffres. Dans ce cas, tout le monde dépasse largement le quota, lorsqu’un quota a été défini.,
création d’une Expression de Table commune récursive
un CTE récursif est un CTE qui se référence lui-même dans ce CTE. Le CTE récursif est utile lorsque vous travaillez avec des données hiérarchiques car le CTE continue à s’exécuter jusqu’à ce que la requête renvoie la hiérarchie entière.
Un exemple typique de données hiérarchiques est une table qui contient une liste d’employés. Pour chaque employé, le tableau fournit une référence au gestionnaire de cette personne. Cette référence est elle-même un ID d’employé dans la même table., Vous pouvez utiliser un CTE récursif pour afficher la hiérarchie des données des employés, telle qu’elle apparaît dans l’organigramme.
notez qu’un CTE créé incorrectement pourrait entrer dans une boucle infinie. Pour éviter cela, vous pouvez inclure L’indice MAXRECURSION dans la clause OPTION de L’instruction principale SELECT, INSERT, UPDATE, DELETE ou MERGE. Pour plus d’informations sur L’utilisation des conseils de requête, consultez la rubrique « Conseils de requête (Transact-SQL)” dans SQL Server Books Online.,
pour démontrer le fonctionnement du CTE récursif, j’ai utilisé les instructions Transact-SQL suivantes pour créer et remplir la table Employees dans la base de données AdventureWorks2008:
comme vous pouvez le réaliser, la base de données AdventureWorks2008 inclut déjà les ressources humaines.Table Employee. Cependant, cette table utilise maintenant le type de données hierarchyid pour stocker des données hiérarchiques, ce qui introduirait une complexité inutile lorsque vous essayez de démontrer un CTE récursif. Pour cette raison, j’ai créé ma propre table., Cependant, si vous souhaitez essayer un CTE récursif sans créer et remplir une nouvelle table, vous pouvez utiliser L’exemple de base de données AdventureWorks fourni avec SQL Server 2005. Les Ressourceshumaines.Employee table dans cette base de données stocke les données d’une manière similaire à la table que je crée ci-dessus.,div>2
Comme vous pouvez le voir, le CTE renvoie cinq colonnes: EmpID, FirstName, LastName, MgrID, et EmpLevel. La colonne EmpLevel fait référence au niveau de la hiérarchie dans lequel les employés s’inscrivent. Le niveau le plus élevé de la hiérarchie est 1, le niveau suivant est 2, suivi de 3, et ainsi de suite.
la requête CTE est elle-même composée de deux instructions SELECT, liées à L’opérateur UNION ALL., Une requête CTE récursive doit contenir au moins deux membres (instructions), connectés par L’opérateur UNION ALL, UNION, INTERSECT ou EXCEPT. Dans cet exemple, la première instruction SELECT est le membre d’ancrage et la deuxième instruction est le membre récursif. Tous les membres d’ancrage doivent précéder les membres récursifs, et seuls les membres récursifs peuvent référencer le CTE lui-même. De plus, tous les membres doivent renvoyer le même nombre de colonnes avec les types de données correspondants.
regardons maintenant de plus près les déclarations elles-mêmes., La première instruction, le membre d’ancrage, récupère L’ID de l’employé, le prénom, le nom de famille et L’ID du gestionnaire dans la table des employés, où l’ID du gestionnaire est null. Ce serait l’employé au sommet de la hiérarchie, ce qui signifie que cette personne ne rend compte à personne. Par conséquent, la valeur de l’ID du gestionnaire est null. Pour refléter que cette personne est au sommet de la hiérarchie, j’attribue une valeur de 1 à la colonne EmpLevel.
la deuxième instruction de la requête CTE-le membre récursif-récupère également l’ID de l’employé, le prénom, le nom et L’ID du gestionnaire pour les employés dans la table des employés., Cependant, notez que je rejoins la table des employés au CTE lui-même. En outre, la jointure est basée sur l’ID manager dans la table Employees et l’ID employee dans le CTE. Ce faisant, le CTE parcourra la table des employés jusqu’à ce qu’il renvoie toute la hiérarchie.
un autre élément à remarquer à propos de la deuxième instruction est que, pour la colonne EmpLevel, j’ajoute la valeur 1 à la valeur EmpLevel telle qu’elle apparaît dans le CTE. De cette façon, chaque fois que l’instruction parcourt la hiérarchie, le niveau correct suivant est appliqué aux employés du niveau.,
Après avoir défini ma clause WITH, je crée une instruction SELECT qui récupère les données du CTE. Notez cependant que pour la colonne Manager, je récupère le prénom et le nom de l’employé associés à l’ID manager dans le CTE. Cela me permet d’afficher le nom complet du gestionnaire pour chaque employé. Le tableau suivant montre le jeu de résultats renvoyé par L’instruction SELECT et son CTE.,
comme vous pouvez le voir, le CTE, récursif ou non, peut être un outil utile lorsque vous devez générer des ensembles de résultats temporaires accessibles dans une instruction SELECT, INSERT, UPDATE, DELETE ou MERGE. Dans un sens, un CTE est comme une table dérivée: il n’est pas stocké en tant qu’objet et n’est valide que lors de l’exécution de l’instruction primaire. Cependant, contrairement à la table dérivée, un CTE peut être référencé plusieurs fois dans une requête et il peut être auto-référencé. Et le meilleur de tous, CTEs sont relativement faciles à mettre en œuvre.,
Vous aurez remarqué que Bob utilise AdventureWorks2008 plutôt que AdventureWorks. Si vous préférez exécuter ces exemples sur la base de données AdventureWorks plutôt que sur la base de données AdventureWorks2008, vous devez remplacer la colonne BusinessEntityID par la colonne SalesPersonID.