logo

TechCodeview

Expressió de taula comuna (CTE) a SQL Server

Utilitzarem les expressions de taula comuns o CTE d'SQL Server per facilitar les unions i les subconsultes complexes. També proporciona una manera de consultar dades jeràrquiques, com ara una jerarquia organitzativa. Aquest article ofereix una visió general completa de CTE, tipus de CTE, avantatges, desavantatges i com utilitzar-los a SQL Server.

Què és CTE a SQL Server?

Un CTE (Common Table Expression) és un conjunt de resultats únic que només existeix durant la durada de la consulta. . Ens permet fer referència a dades dins d'un únic àmbit d'execució de la instrucció SELECT, INSERT, UPDATE, DELETE, CREATE VIEW o MERGE. És temporal perquè el seu resultat no es pot emmagatzemar enlloc i es perdrà tan bon punt es completi l'execució d'una consulta. Va venir per primera vegada amb la versió SQL Server 2005. Un DBA sempre ha preferit que el CTE l'utilitzi com a alternativa a una subconsulta/visualització. Segueixen l'estàndard ANSI SQL 99 i són compatibles amb SQL.

Sintaxi CTE a SQL Server

La sintaxi CTE inclou un nom CTE, una llista de columnes opcional i una instrucció/consulta que defineix l'expressió de taula comuna (CTE). Després de definir el CTE, el podem utilitzar com a vista en una consulta SELECT, INSERT, UPDATE, DELETE i MERGE.

La següent és la sintaxi bàsica de CTE a SQL Server:

 WITH cte_name (column_names) AS (query) SELECT * FROM cte_name;

En aquesta sintaxi:

  • Primer hem especificat el nom del CTE al qual es farà referència més endavant en una consulta.
  • El següent pas és crear una llista de columnes separades per comes. Assegura que el nombre de columnes dels arguments de definició CTE i el nombre de columnes de la consulta han de ser el mateix. Si no hem definit les columnes dels arguments CTE, utilitzarà les columnes de consulta que defineixen el CTE.
  • Després d'això, utilitzarem la paraula clau AS després del nom de l'expressió i després definirem una instrucció SELECT el conjunt de resultats de la qual omple el CTE.
  • Finalment, utilitzarem el nom CTE en una consulta com SELECT, INSERT, UPDATE, DELETE i MERGE.

S'ha de tenir en compte mentre escriu la definició de la consulta CTE; no podem utilitzar les clàusules següents:

  1. ORDER BY tret que també utilitzeu com a clàusula TOP
  2. INTO
  3. clàusula OPTION amb suggeriments de consulta
  4. PER NAVEGAR

La imatge següent és la representació de la definició de la consulta CTE.

CTE a SQL Server

Aquí, la primera part és una expressió CTE que conté una consulta SQL que es pot executar de manera independent a SQL. I la segona part és la consulta que utilitza el CTE per mostrar el resultat.

Exemple

Entendrem com funciona CTE a SQL Server utilitzant diversos exemples. Aquí, farem servir una taula ' client 'per a una manifestació. Suposem que aquesta taula conté les dades següents:

CTE a SQL Server

En aquest exemple, el nom CTE és clients_a_novayork , la subconsulta que defineix el CTE retorna les tres columnes nom del client, correu electrònic, i estat . Com a resultat, el CTE customers_in_newyork retornarà tots els clients que visquin a l'estat de Nova York.

Després de definir el CTE customers_in_newyork, l'hem fet referència al SELECCIONA declaració per obtenir els detalls d'aquells clients que es troben a Nova York.

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York') SELECT c_name, email, state FROM customers_in_NewYork;

Després d'executar la instrucció anterior, donarà la següent sortida. Aquí, podem veure que el resultat només retorna la informació del client que es troba a l'estat de Nova York.

CTE a SQL Server

CTE múltiple

En alguns casos, haurem de crear diverses consultes CTE i unir-les per veure els resultats. Podem utilitzar diversos conceptes de CTE en aquest escenari. Hem d'utilitzar l'operador de coma per crear diverses consultes CTE i combinar-les en una sola instrucció. L'operador de coma ',' ha d'anar precedit del nom CTE per distingir diversos CTE.

Diversos CTE ens ajuden a simplificar les consultes complexes que finalment s'ajunten. Cada peça complexa tenia el seu propi CTE, que després es podia referenciar i unir fora de la clàusula WITH.

NOTA: La definició de CTE múltiple es pot definir mitjançant UNION, UNION ALL, JOIN, INTERSECT o EXCEPT.

La sintaxi següent ho explica amb més claredat: 

 WITH cte_name1 (column_names) AS (query), cte_name2 (column_names) AS (query) SELECT * FROM cte_name UNION ALL SELECT * FROM cte_name;

Exemple

Entendrem com funcionen diversos CTE a SQL Server. Aquí, farem servir l'anterior ' client ' taula per a una demostració.

En aquest exemple, hem definit els dos noms CTE clients_a_novayork i clients_a_california . Aleshores, el conjunt de resultats de subconsultes d'aquests CTE omple el CTE. Finalment, utilitzarem els noms de CTE en una consulta que retornarà tots els clients que es troben a Nova York i Estat de Califòrnia .

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York'), customers_in_California AS (SELECT * FROM customer WHERE state = 'California') SELECT c_name, email, state FROM customers_in_NewYork UNION ALL SELECT c_name, email, state FROM customers_in_California;

Estat de Nova York i Califòrnia.

CTE a SQL Server

Per què necessitem CTE?

Igual que les visualitzacions de bases de dades i les taules derivades, els CTE poden facilitar l'escriptura i la gestió de consultes complexes fent-les més llegibles i senzilles. Podem aconseguir aquesta característica desglossant les consultes complexes en blocs simples que es poden reutilitzar per reescriure la consulta.

Alguns dels seus casos d'ús es mostren a continuació:

  • És útil quan necessitem definir una taula derivada diverses vegades dins d'una sola consulta.
  • És útil quan necessitem crear una alternativa a una vista a la base de dades.
  • És útil quan necessitem realitzar el mateix càlcul diverses vegades en diversos components de consulta simultàniament.
  • És útil quan necessitem utilitzar funcions de classificació com ROW_NUMBER(), RANK() i NTILE().

Alguns dels seus avantatges es detallen a continuació:

vaja
  • CTE facilita el manteniment del codi més fàcil.
  • CTE augmenta la llegibilitat del codi.
  • Augmenta el rendiment de la consulta.
  • CTE permet implementar consultes recursives fàcilment.

Tipus de CTE a SQL Server

SQL Server divideix el CTE (Common Table Expressions) en dues grans categories:

  1. CTE recursiu
  2. CTE no recursiu

CTE recursiu

Una expressió de taula comuna es coneix com a CTE recursiu que fa referència a si mateix. El seu concepte es basa en la recursivitat, que es defineix com ' l'aplicació d'un procés recursiu o definició repetidament .' Quan executem una consulta recursiva, itera repetidament sobre un subconjunt de dades. Simplement es defineix com una consulta que es diu a si mateixa. Hi ha una condició final en algun moment, de manera que no s'anomena infinitament.

Un CTE recursiu ha de tenir a UNIÓ TOTS declaració i una segona definició de consulta que fa referència al propi CTE per tal de ser recursiu.

Exemple

Entenem com funciona CTE recursiu a SQL Server. Considereu la declaració següent, que genera una sèrie dels cinc primers nombres senars: 

 WITH odd_num_cte (id, n) AS ( SELECT 1, 1 UNION ALL SELECT id+1, n+2 from odd_num_cte where id <5 ) select * from odd_num_cte; < pre> <p>When we execute this recursive CTE, we will see the output as below:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-5.webp" alt="CTE in SQL Server"> <p>The below example is the more advanced recursive CTE. Here, we are going to use the &apos; <strong>jtp_employees</strong> &apos; table for a demonstration that contains the below data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-6.webp" alt="CTE in SQL Server"> <p>This example will display the hierarchy of employee data. Here table provides a reference to that person&apos;s manager for each employee. The reference is itself an employee id within the same table.</p> <pre> WITH cte_recursion (EmpID, FirstName, LastName, MgrID, EmpLevel) AS ( SELECT EmployeeID, FirstName, LastName, ManagerID, 1 FROM jtp_employees WHERE ManagerID IS NULL UNION ALL SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.ManagerID, r.EmpLevel + 1 FROM jtp_employees emp INNER JOIN cte_recursion r ON emp.ManagerID = r.EmpID ) SELECT FirstName + &apos; &apos; + LastName AS FullName, EmpLevel, (SELECT FirstName + &apos; &apos; + LastName FROM jtp_employees WHERE EmployeeID = cte_recursion.MgrID) AS Manager FROM cte_recursion ORDER BY EmpLevel, MgrID </pre> <p>This CTE will give the following output where we can see the hierarchy of employee data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-7.webp" alt="CTE in SQL Server"> <h3>Non-Recursive CTE</h3> <p>A common table expression that doesn&apos;t reference itself is known as a non-recursive CTE. A non-recursive CTE is simple and easier to understand because it does not use the concept of recursion. According to the CTE Syntax, each CTE query will begin with a &apos; <strong>With</strong> &apos; clause followed by the CTE name and column list, then AS with parenthesis.</p> <h2>Disadvantages of CTE</h2> <p>The following are the limitations of using CTE in SQL Server:</p> <ul> <li>CTE members are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.</li> <li>The CTE can only be referenced once by the Recursive member.</li> <li>We cannot use the table variables and CTEs as parameters in stored procedures.</li> <li>We already know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.</li> <li>Since it&apos;s just a shortcut for a query or subquery, it can&apos;t be reused in another query.</li> <li>The number of columns in the CTE arguments and the number of columns in the query must be the same.</li> </ul> <hr></5>

Aquest CTE donarà la següent sortida on podem veure la jerarquia de les dades dels empleats:

CTE a SQL Server

CTE no recursiu

Una expressió de taula comuna que no fa referència a si mateixa es coneix com a CTE no recursiu. Un CTE no recursiu és senzill i més fàcil d'entendre perquè no utilitza el concepte de recursivitat. Segons la sintaxi CTE, cada consulta CTE començarà amb un ' Amb clàusula seguida del nom del CTE i la llista de columnes, després AS amb parèntesis.

Inconvenients del CTE

A continuació es mostren les limitacions de l'ús de CTE a SQL Server:

  • Els membres del CTE no poden utilitzar les clàusules de paraules clau com Distinct, Group By, Having, Top, Joins, etc.
  • El membre recursiu només pot fer referència al CTE una vegada.
  • No podem utilitzar les variables de la taula i els CTE com a paràmetres en els procediments emmagatzemats.
  • Ja sabem que el CTE es podria utilitzar en lloc d'una vista, però un CTE no es pot niar, mentre que les vistes sí.
  • Com que només és una drecera per a una consulta o subconsulta, no es pot reutilitzar en una altra consulta.
  • El nombre de columnes dels arguments CTE i el nombre de columnes de la consulta han de ser el mateix.