logo

TechCodeview

CTE en SQL

El Expressions de taula comunes (CTE) es van introduir a l'SQL estàndard per tal de simplificar diverses classes de consultes SQL per a les quals una taula derivada no era adequada. CTE es va introduir a SQL Server 2005, l'expressió de taula comuna (CTE) és un conjunt de resultats amb nom temporal al qual podeu fer referència dins d'un SELECCIONA , INSERT , ACTUALITZACIÓ , o ELIMINAR declaració. També podeu utilitzar un CTE en una vista CREATE, com a part de la consulta SELECT de la vista. A més, a partir de l'SQL Server 2008, podeu afegir un CTE a la nova instrucció MERGE.

Per què necessitem CTE al servidor SQL?

Una taula comuna [obsoleta] CTE és potent SQL construcció que ajuda a simplificar les consultes. Els CTE actuen com a taules virtuals (amb registres i columnes) que es creen durant l'execució de la consulta, s'utilitzen per la consulta i s'eliminen després de l'execució de la consulta.

Utilitzant el CTE

Podem definir CTE afegint una clàusula WITH directament abans de la instrucció SELECT, INSERT, UPDATE, DELETE o MERGE. La clàusula WITH pot incloure un o més CTE separats per comes.



img css align

Sintaxi:

[AMB [, …]]

::=

git checkout

nom_cte [(nom_columna [, …])]

AS (cte_query)

Argumentació

  1. Nom de l'expressió: Un identificador vàlid per a una expressió de taula comuna. El nom_expressió ha de ser diferent dels noms d'altres expressions de taula comunes definides a la mateixa clàusula WITH, però el nom_expressió pot ser el mateix que el nom de la taula o vista base. Totes les referències a expression_name a la consulta utilitzen l'expressió de taula comuna en lloc de l'objecte base.
  2. Nom de la columna: Especifica un nom de columna en una expressió de taula comuna. No es permeten els noms duplicats dins d'una única definició de CTE. El nombre de noms de columnes ha de coincidir amb el nombre de columnes del conjunt de resultats de CTE_query_definition. Una llista de noms de columnes només és opcional si totes les columnes resultants de la definició de la consulta tenen noms diferents.
  3. CTE_QueryDefinition: Especifica una instrucció SELECT el conjunt de resultats de la qual compleix una expressió de taula comuna. La instrucció SELECT per a CTE_query_defining ha de complir els mateixos requisits que la creació d'una vista, excepte que un CTE no pot definir un altre CTE. Per obtenir més informació, consulteu la secció Observacions i CREATE VIEW (Transact-SQL). Si es defineixen diversos CTE_query_settings, les definicions de consulta s'han de combinar amb un dels operadors de conjunt UNION ALL, UNION, EXCEPT o INTERSECT.

Regles per definir i utilitzar expressions de taula comuns recursives

Les directrius següents s'apliquen per definir expressions de taula comunes recursives:

  1. Una definició de CTE recursiva ha de contenir almenys dues definicions de consulta de CTE, un membre d'ancoratge i un membre recursiu. Podeu definir diversos membres d'ancoratge i recursius. Tanmateix, totes les definicions de consulta de membres d'ancoratge s'han de col·locar abans de la primera definició de membre recursiu. Totes les definicions de consulta de CTE són membres d'ancoratge tret que facin referència al mateix CTE.
  2. Els elements d'ancoratge s'han de combinar amb un dels operadors de conjunt següents: UNION TOT, UNION, INTERSECT, EXCEPTE. UNION ALL és l'únic operador de conjunt vàlid entre l'últim membre d'ancoratge i el primer membre recursiu quan s'uneix a diversos membres recursius. Els elements d'ancoratge i recursius han de tenir el mateix nombre de columnes.
  3. El tipus de dades de les columnes del membre recursiu ha de ser el mateix que el tipus de dades de la columna corresponent del membre d'ancoratge.
  4. Els elements següents no es permeten a la seva CTE_query_definition per als membres recursius:
Please choose a different grouping PIVOT (For database compatibility level 110 and above.  See Breaking Changes to Database Engine Features in SQL Server 2016. Offal Scalar aggregation Up LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed) subquery A hint applied to recursive references to CTEs within a CTE_query_definition.>

Creació d'una expressió de taula comuna recursiva

Un CTE recursiu és aquell que es fa referència a si mateix dins d'aquest CTE. El CTE recursiu és útil quan es treballa amb dades jeràrquiques, ja que el CTE continua executant-se fins que la consulta retorna tota la jerarquia.

Un exemple típic de dades jeràrquiques és una taula que inclou una llista d'empleats. Per a cada empleat, la taula proporciona una referència al responsable d'aquesta persona. Aquesta referència és en si mateixa un identificador d'empleat dins de la mateixa taula. Podeu utilitzar un CTE recursiu per mostrar la jerarquia de les dades dels empleats.

Si un CTE es crea incorrectament, pot entrar en un bucle infinit. Per evitar-ho, es pot afegir la pista MAXRECURSION a la clàusula OPTION de la instrucció primària SELECT, INSERT, UPDATE, DELETE o MERGE.

Es crea una taula: 

CREATE TABLE Employees (  EmployeeID int NOT NULL PRIMARY KEY,  FirstName varchar(50) NOT NULL,  LastName varchar(50) NOT NULL,  ManagerID int NULL )  INSERT INTO Employees VALUES (1, 'Ken', 'Thompson', NULL) INSERT INTO Employees VALUES (2, 'Terri', 'Ryan', 1) INSERT INTO Employees VALUES (3, 'Robert', 'Durello', 1) INSERT INTO Employees VALUES (4, 'Rob', 'Bailey', 2) INSERT INTO Employees VALUES (5, 'Kent', 'Erickson', 2) INSERT INTO Employees VALUES (6, 'Bill', 'Goldberg', 3) INSERT INTO Employees VALUES (7, 'Ryan', 'Miller', 3) INSERT INTO Employees VALUES (8, 'Dane', 'Mark', 5) INSERT INTO Employees VALUES (9, 'Charles', 'Matthew', 6) INSERT INTO Employees VALUES (10, 'Michael', 'Jhonson', 6)>

Després de crear la taula Employees, es crea la següent instrucció SELECT, que va precedida per una clàusula WITH que inclou un CTE anomenat cteReports:

propietats àcides en dbms 
WITH  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel)  AS  (  SELECT EmployeeID, FirstName, LastName, ManagerID, 1  FROM Employees  WHERE ManagerID IS NULL  UNION ALL  SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,   r.EmpLevel + 1  FROM Employees e  INNER JOIN cteReports r  ON e.ManagerID = r.EmpID  ) SELECT  FirstName + ' ' + LastName AS FullName,   EmpLevel,  (SELECT FirstName + ' ' + LastName FROM Employees   WHERE EmployeeID = cteReports.MgrID) AS Manager FROM cteReports  ORDER BY EmpLevel, MgrID>

Així, els CTE poden ser útils quan necessiteu generar conjunts de resultats temporals als quals es pot accedir en una instrucció SELECT, INSERT, UPDATE, DELETE o MERGE.

Característiques i limitacions de les expressions de taula comunes a Azure

La implementació actual de CTE a Azure Synapse Analytics and Analytics Platform System (PDW) té les característiques i limitacions següents:

  1. Un CTE només es pot especificar en una instrucció SELECT.
  2. Un CTE només es pot especificar en una instrucció CREATE VIEW.
  3. Un CTE només es pot especificar en una instrucció CREATE TABLE AS SELECT (CTAS).
  4. Un CTE només es pot especificar en una instrucció CREATE REMOTE TABLE AS SELECT (CRTAS).
  5. Un CTE només es pot especificar en una instrucció CREATE EXTERNAL TABLE AS SELECT (CETAS).