SQLShack (Français)

Qu’est-ce qu’une Expression de Table commune

une Expression de Table commune, également appelée CTE sous forme abrégée, est un ensemble de résultats nommé temporaire que vous pouvez référencer dans une instruction SELECT, INSERT, UPDATE ou DELETE. Le CTE peut également être utilisé dans une vue.

dans cet article, nous verrons en détail comment créer et utiliser CTEs à partir de notre serveur SQL.,

syntaxe et exemples d’Expressions de Table courantes

la requête CTE commence par un « With” et est suivie du nom de l’Expression. Nous utiliserons ce nom d’expression dans notre requête select pour afficher le résultat de notre requête CTE et écrire notre définition de requête CTE.,

To view the CTE result we use a Select query with the CTE expression name.,r

Common Table Expression (CTE) Types

There are two types of CTEs: Recursive and Non-Recursive

Non-Recursive CTEs

Non-Recursive CTEs are simple where the CTE doesn’t use any recursion, or repeated processing in of a sub-routine., Nous allons créer un CTE Non récursif simple pour afficher le numéro de ligne de 1 à 10.

selon la syntaxe CTE, chaque requête CTE commencera par un « With” suivi du nom de L’Expression CTE avec la liste des colonnes.

ici, nous n’avons utilisé qu’une seule colonne comme ROWNO. Vient ensuite la partie requête, Ici nous écrivons notre requête select à exécuter pour notre CTE. Après avoir créé notre requête CTE pour exécuter le CTE, utilisez l’instruction select avec le nom de L’Expression CTE.

sortie: lorsque nous exécutons la requête, nous pouvons voir la sortie ci-dessous.,

CTE récursif

Les Cte récursifs utilisent des boucles procédurales répétées alias récursivité. La requête récursive s’appelle jusqu’à ce que la requête remplisse la condition. Dans un CTE récursif, nous devrions fournir une condition where pour terminer la récursivité.:

nous verrons comment créer une requête récursive simple pour afficher le numéro de ligne de 1 à 10 en utilisant un CTE.

Tout d’abord, nous déclarons la variable entière comme « RowNo” et définissons la valeur par défaut comme 1 et nous avons créé notre première requête CTE en tant que nom d’expression, « ROWCTE”., Dans notre CTE, nous allons d’abord afficher le numéro de ligne par défaut et ensuite, nous utiliserons une Union ALL pour incrémenter et afficher le numéro de ligne 1 de un jusqu’à ce que la ligne No atteigne la valeur incrémentée à 10. Pour afficher le résultat, nous utiliserons une requête select pour afficher notre résultat CTE.,/div>

Résultat: Lors de l’exécution de la requête, nous pouvons voir ci-dessous la sortie.,

requête CTE pour afficher la plage de dates:

considérons qu’il existe un scénario pour afficher la date de la date de début à la date de fin une par une Comme chaque ligne avec des détails. Afin d’afficher les données récursives, nous utiliserons la requête CTE.

ici, nous allons écrire une requête CTE pour afficher la plage de dates avec le numéro de la semaine et le jour. Pour cela, nous définissons la date de début et de fin dans le paramètre. Ici, dans cet exemple, nous avons utilisé le getdate() pour définir la date de début date d’Aujourd’hui, et pour la date de fin de nous ajouter 16 jours à partir d’aujourd’hui.,

CTE sans Union All

ici, nous pouvons voir que nous avons créé une requête CTE simple pour afficher le RowNo, la date de début et le numéro de semaine. Lorsque nous exécutons cela, nous n’obtiendrons qu’un seul résultat avec RowNo comme « 1” ,StartDate comme date actuelle et numéro de semaine avec le jour de la semaine.

sortie: lorsque nous exécutons la requête, nous pouvons voir la sortie ci-dessous.,

CTE avec Union All

afin d’afficher le résultat de la date de début à la date de fin un par un comme récursif, nous utilisons un Union All pour incrémenter RowNo, pour ajouter le jour un par un jusqu’à ce que la condition satisfait la plage de dates, afin Dans cet exemple, nous répétons la récursivité pour afficher nos enregistrements jusqu’à ce que la date soit inférieure ou égale à la date de fin.

sortie: lorsque nous exécutons la requête, nous pouvons voir la sortie ci-dessous.,

plusieurs CTE

dans certains scénarios, nous devons créer plus d’une requête CTE et les joindre pour afficher notre résultat. Dans ce cas, nous pouvons utiliser les multiples CTEs. Nous pouvons créer une requête CTE multiple et les combiner en une seule requête en utilisant la virgule. Plusieurs CTE doivent être séparés par »,  » virgule en jachère par le nom CTE.

Nous utiliserons ci-dessus le même exemple de plage de dates pour utiliser plus d’une requête CTE, ici nous pouvons voir que nous avons créé deux requêtes CTE comme CTE1 et CTE 2 pour afficher le résultat de la plage de dates pour CTE1 et pour CTE2.,

exemple:

sortie: lorsque nous exécutons la requête, nous pouvons voir la sortie ci-dessous.

Utilisation de la CTE de la requête SQL de la Table

Maintenant, nous allons voir comment utiliser CTE requête pour notre table SQL server données.,

Créer une Base de données: tout d’Abord, nous avons créer une base de données pour la création de notre table

créer une table: nous créons maintenant un exemple de table d’éléments sur la base de données créée.

insérer des exemples de données: nous allons insérer quelques exemples d’enregistrements à utiliser dans notre requête CTE.,

exemple CTE:

maintenant, nous allons créer un résultat temporaire simple en utilisant la requête CTE. Ici, dans cette requête CTE, nous avons donné le nom de l’expression comme « itemCTE » et nous avons ajouté la liste des colonnes que nous utilisons dans la requête CTE. Dans la requête CTE, nous affichons tous les détails de l’article avec l’année.

sortie: lorsque nous exécutons la requête, nous pouvons voir la sortie ci-dessous.

CTE utilisant Union ALL

considérons qu’il existe deux scénarios ci-dessous pour afficher le résultat.

1. le premier scénario consiste à afficher le prix de chaque article de l’année en cours.,
2. Le deuxième scénario consiste à augmenter de 10% le prix de chaque article pour l’année prochaine.

pour cela, nous utilisons la requête CTE ci-dessus. Dans cette requête, nous ajoutons L’UNION ALL et dans UNION ALL Query, nous calculons pour ajouter 10% au prix de chaque article et afficher dans la ligne suivante avec l’ajout d’un an.

sortie: lorsque nous exécutons la requête, nous pouvons voir la sortie ci-dessous.

les Expressions de Table Communes (CTE) pour Insérer

Maintenant, nous allons voir comment insérer le CTE résultat à une autre table. Pour cela, considérons notre tableau D’articles ci-dessus., Nous insérons le résultat des détails de L’élément de la requête CTE ci-dessus dans la table D’Historique des éléments. Pour cela, nous créons d’abord une table D’Historique des éléments.

créer une table D’historique D’article: dans cette table d’historique, nous ajoutons les mêmes colonnes que la table d’article ainsi que la colonne MarketRate comme prix d’article présent ou futur. Voici la requête pour créer une table ItemHistory.

Cte Insert exemple:

ici, nous utilisons ci-dessus la même requête Cte insérer le résultat dans la table D’Historique des éléments. À partir de cette requête, nous insérons à la fois les détails de l’article du prix de l’article de l’année actuelle et les prix de l’article de l’année suivante ajoutés comme 10% de plus.,

sortie: lorsque nous exécutons la requête, nous pouvons voir la sortie ci-dessous car 30 enregistrements ont été insérés dans notre table D’Historique des éléments.

sélectionnez requête:

pour afficher le résultat de l’historique des éléments, nous sélectionnons et affichons tous les détails.,

Résultat: Lors de l’exécution de la requête, nous pouvons voir ci-dessous la sortie à partir de l’historique de l’élément table.

Créer une Vue avec CTE Exemple:

Maintenant, nous voyons comment les utiliser au-dessus de la CTE requête peut être utilisée dans une vue., Ici, nous créons une vue et nous ajoutons le résultat CTE à l’intérieur de la vue. Lorsque nous sélectionnons la vue en conséquence, nous pouvons voir que la sortie CTE sera affichée.

exemple de requête:

sortie: lorsque nous exécutons la requête, nous pouvons voir la sortie ci-dessous comme résultat de la vue.

Comment écrire un propre CTE Requête:

Voici quelques directives de base que vous devez suivre pour écrire une bonne CTE Requête.

1. un CTE doit être suivi d’une seule instruction SELECT, INSERT, UPDATE ou DELETE qui fait référence à certaines ou à toutes les colonnes CTE.,
2. plusieurs définitions de requête CTE peuvent être définies dans un CTE non récursif.
3. un CTE peut se référencer et des CTEs précédemment définis dans la même clause WITH
4. nous ne pouvons utiliser Qu’une seule Clause With dans un CTE
5. ORDER BY, INTO, COMPUTE or COMPUTE BY, OPTION, FOR XML cannot be used in non-recursive CTE query definition
6. SELECT DISTINCT, GROUP BY, HAVING, scalar aggregation, TOP, LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed) subqueries cannot be used in a recursive CTE query definition.,

Conclusion

CTEs peut être utilisé pour créer une requête récursive et peut être utilisé pour se référencer plusieurs fois. CTEs peut être utilisé à la place des vues et enfin un CTE est facile et simple pour la lisibilité et la maintenabilité du code.

• Auteur
• Derniers Posts

Syed Shanu est un MVP Microsoft, deux fois CsharpCorner le titre de MVP et de deux projet de Code MVP, Auteur, Blogueur, Conférencier et toujours heureux de partager ce qu’il sait pour les autres.,
Il est essentiellement de Madurai, Tamil Nadu, Inde. Il travaille en tant que Responsable technique en Corée du Sud avec plus de 10 ans d’expérience sur les technologies Microsoft.
Shanu est actif dans la communauté et toujours heureux de partager des sujets liés à ASP.NET, MVC, ASP.NET il est possible d’utiliser le serveur de base, L’API web, le serveur SQL, L’UWP, L’Azure, le C#, L’AngularJs, L’Angular2. Il a écrit plus de 70 articles avec diverses technologies sur ASP.NET, serveur SQL, C#, AngularJs, Angular2, ASP.NET Core, Unity 3D, Windows Universal App et Samsung Gear App développement., Il est plusieurs fois vainqueur de TechNet Guru Gold et vous pouvez trouver toute sa contribution dans MSDN et aussi dans TechNet Wiki https://social.technet.microsoft.com/profile/syedshanu/
Il est également auteur de l’eBook « SQL Queries For Beginners » vous pouvez télécharger l’eBook à partir d’ici: http://www.c-sharpcorner.com/ebooks/sql-queries-for-beginners.,
Afficher tous les articles par Syed Shanu

Derniers messages par Syed Shanu (voir tous)

• SQL Server Expressions de Table Communes (CTE) – 23 février 2017
• Création d’un « smart” de déclenchement en fonction de la piste d’audit pour SQL Server – 12 décembre, 2016