Travailler avec des expressions de table communes (CTES) dans SQL

CTE (Expression de la table commune) est un outil de SQL pour diviser les requêtes complexes, améliorer la lisibilité et la maintenance. Il s'agit d'un ensemble de résultats temporaire qui peut être référencé plusieurs fois dans une seule requête, avec une structure claire, particulièrement adaptée aux sous-requêtes imbriquées et au traitement récursif des données. La syntaxe de base est avec CTE_NAME AS (Instruction de requête) SELECT * FROM CTE_NAME; Par exemple, lorsque vous comptez le nombre d'employés du département, vous pouvez d'abord définir le CTE DEPT_COUNT, puis filtrer les résultats. L'utilisation du CTE peut démonter la logique en étapes, telles que le calcul du salaire moyen de chaque département, puis trouver des employés qui sont supérieurs au salaire moyen, ce qui rend le code plus facile à comprendre. Le CTE prend également en charge les requêtes récursives, adaptées aux données de la structure des arbres, dans le format avec CTE_name récursif AS (Union All Recursive Query), comme trouver un employé et tous les subordonnés. Les notes comprennent: la partie récursive doit se référer et avoir des conditions de terminaison; Différents niveaux de support de base de données sont différents; Le CTE n'est pas matérialisé et les références répétées peuvent affecter les performances; éviter les conflits avec les noms de table; et l'attention doit être accordée à la version de la base de données en termes de compatibilité. Il est recommandé de donner la priorité à l'utilisation du CTE lorsque vous devez démonter la logique, les requêtes récursives ou améliorer la lisibilité.

CTE (Expression de la table commune) est un outil très pratique de SQL qui vous permet de diviser des requêtes complexes en parties plus lisibles et maintenables. Surtout lorsque vous traitez des sous-requêtes imbriquées ou des données récursives, les avantages de la structure claire de CTE sont très évidents.

Qu'est-ce que CTE?

CTE est un ensemble de résultats temporaire qui peut être référencé plusieurs fois dans une requête. Ce n'est pas un tableau physique, mais une "vue temporaire" logique, et sa portée est limitée à la requête qui le définit.

La syntaxe de base est la suivante:

 Avec cte_name comme (
    - déclaration de requête)
SELECT * FROM CTE_NAME;

Par exemple, si vous souhaitez compter le nombre d'employés dans chaque département, vous pouvez l'écrire comme ceci:

 Avec dept_count comme (
    Sélectionnez Department_id, Count (*) comme num_employes
    Des employés
    Groupe par département_id
)
SELECT * FROM DEPT_COUNT WHERE NUM_EMPLOYEES> 5;

Cela a non seulement une structure claire, mais facilite également le débogage et la réutilisation.

Améliorer la lisibilité du code à l'aide de CTE

Beaucoup de gens aiment utiliser des sous-requêtes imbriquées pour résoudre des problèmes au début, mais une fois qu'il y a plus de niveaux, SQL devient difficile à comprendre et à maintenir. Et l'utilisation du CTE peut être écrite de couche par couche, et chaque pièce est claire et claire.

Par exemple: Supposons que vous souhaitiez trouver des employés dont le salaire est supérieur au salaire moyen de votre département. Si vous écrivez avec une sous-requête, vous pouvez avoir imbriqué trois couches, ce qui semble très déroutant; Mais si vous utilisez CTE:

 Avec avg_salary comme (
    Sélectionnez Department_id, AVG (Salaire) comme AVG_SAL
    Des employés
    Groupe par département_id
),
high_earners as (
    Sélectionner e. *
    Des employés e
    Rejoignez AVG_SALARY A sur E.Deartment_ID = A.DEPARTMENT_ID
    Où e.salary> a.avg_sal
)
SELECT * dans High_earners;

Écrivez-le en étapes et d'autres sauront ce que vous faites en un coup d'œil.

CTE récursif

L'une des fonctions les plus puissantes du CTE est qu'elle prend en charge les requêtes récursives, qui conviennent aux structures d'arbres ou aux données hiérarchiques, telles que les structures organisationnelles, les structures de répertoires, etc.

Le format de base est le suivant:

 Avec Cte_name récursif comme (
    - Requête initiale (membre d'ancrage)
    SÉLECTIONNER ...
    Union All
    - requête récursive SELECT ...
)
SELECT * FROM CTE_NAME;

Par exemple, trouvez un employé et tous ses subordonnés:

 Avec des subordonnés récursifs comme (
    Sélectionnez Employee_ID, Manager_id, nom
    Des employés
    Où Employee_ID = 100 - Démarrage du syndicat des employés

    Sélectionnez E.Eemployee_ID, E.Manager_id, E.Name
    Des employés e
    INTER JOIN SUBORDATIONS S sur e.manager_id = s.employee_id
)
Sélectionner * dans les subordonnés;

Quelques points à noter:

• La partie récursive doit se référer au CTE lui-même
• Il doit y avoir une condition de terminaison, sinon il y aura des boucles infinies
• Différentes bases de données ont une prise en charge légèrement différente du CTE récursif. Vérifiez le document avant de l'utiliser.

Quelques précautions dans les applications pratiques

Bien que le CTE soit très utile, certains détails doivent être prêts à faire attention:

• Problèmes de performance : le CTE n'est pas une vue matérialisée, et elle sera exécutée à nouveau chaque fois que la référence est référencée. Si vous utilisez plusieurs CTES sur une grande table, et chacun est appelé plusieurs fois, il peut ralentir la requête.
• Nommer les conflits : ne donnez pas au CTE le même nom que le tableau réel, car il est facile d'être confus.
• Compatibilité : Bien que les bases de données grand public prennent en charge CTE (comme PostgreSQL, MySQL 8.0 et SQL Server), certaines versions plus anciennes peuvent ne pas prendre en charge le CTE récursif.

Il est recommandé de hiérarchiser le CTE dans les scénarios suivants:

• La requête est compliquée et nécessite un démontage de la logique
• Une requête récursive est requise
• Veulent améliorer la lisibilité au code et la maintenance

Fondamentalement, c'est tout. Le CTE est une manière très pratique dans l'écriture SQL. Après l'avoir maîtrisé, vous constaterez que de nombreuses requêtes complexes peuvent devenir plus claires et plus faciles à comprendre.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!