logo

TechCodeview

CTE i SQL

De Vanlige tabelluttrykk (CTE) ble introdusert i standard SQL for å forenkle ulike klasser av SQL-spørringer som en avledet tabell bare var uegnet for. CTE ble introdusert i SQL Server 2005, det vanlige tabelluttrykket (CTE) er et midlertidig navngitt resultatsett som du kan referere til i en PLUKKE UT , SETT INN , OPPDATER , eller SLETT uttalelse. Du kan også bruke en CTE i en CREATE-visning, som en del av visningens SELECT-spørring. I tillegg, fra og med SQL Server 2008, kan du legge til en CTE i den nye MERGE-setningen.

Hvorfor trenger vi CTE i SQL-serveren?

En vanlig tabell [avviklet]CTE er en kraftig SQL konstruksjon som hjelper til med å forenkle spørringer. CTE-er fungerer som virtuelle tabeller (med poster og kolonner) som opprettes under kjøring av spørringer, brukes av spørringen og slettes etter at spørringen er utført.

Bruker CTE

Vi kan definere CTE-er ved å legge til en WITH-klausul rett før SELECT-, INSERT-, UPDATE-, DELETE- eller MERGE-setningen. WITH-leddet kan inkludere én eller flere CTE-er atskilt med komma.



apurva padgaonkar

Syntaks:

[Med [, …]]

::=

cte_name [(kolonne_navn [, …])]

AS (cte_query)

Argument

  1. Navn på uttrykk: En gyldig identifikator for et vanlig tabelluttrykk. Uttrykket_navn må være forskjellig fra navnene på andre vanlige tabelluttrykk definert i det samme WITH-leddet, men uttrykksnavnet kan være det samme som navnet på basistabellen eller visningen. Alle referanser til uttrykksnavn i spørringen bruker det vanlige tabelluttrykket i stedet for basisobjektet.
  2. Kolonnenavn: Angir et kolonnenavn i et vanlig tabelluttrykk. Dupliserte navn innenfor en enkelt CTE-definisjon er ikke tillatt. Antall kolonnenavn må samsvare med antall kolonner i CTE_query_definitionens resultatsett. En liste over kolonnenavn er valgfri bare hvis alle de resulterende kolonnene i spørringsdefinisjonen har forskjellige navn.
  3. CTE_QueryDefinition: Spesifiserer en SELECT-setning hvis resultatsett tilfredsstiller et vanlig tabelluttrykk. SELECT-setningen for CTE_query_defining må oppfylle de samme kravene som å opprette en visning, bortsett fra at en CTE ikke kan definere en annen CTE. For mer informasjon, se Merknader-delen og OPPRETT VISNING (Transact-SQL). Hvis flere CTE_query_settings er definert, må spørringsdefinisjonene kombineres med en av settoperatorene UNION ALL, UNION, EXCEPT eller INTERSECT.

Regler for definisjon og bruk av rekursive vanlige tabelluttrykk

Følgende retningslinjer gjelder for å definere rekursive vanlige tabelluttrykk:

  1. En rekursiv CTE-definisjon må inneholde minst to CTE-spørringsdefinisjoner, et ankermedlem og et rekursivt medlem. Du kan definere flere anker- og rekursive medlemmer. Imidlertid må alle ankermedlemspørringsdefinisjoner plasseres før den første rekursive medlemsdefinisjonen. Alle CTE-spørringsdefinisjoner er ankermedlemmer med mindre de refererer til selve CTE.
  2. Ankerelementer må kombineres med en av følgende settoperatorer: UNION ALL, UNION, INTERSECT, EXCEPT. UNION ALL er den eneste gyldige settoperatøren mellom det siste ankermedlemmet og det første rekursive medlemmet når man slår sammen flere rekursive medlemmer. Anker og rekursive elementer må ha samme antall kolonner.
  3. Datatypen til det rekursive medlemmets kolonner må være den samme som datatypen til den tilsvarende kolonnen til ankermedlemmet.
  4. Følgende elementer er ikke tillatt i hennes CTE_query_definition for rekursive medlemmer:
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.>

Opprette et rekursivt felles tabelluttrykk

En rekursiv CTE er en som refererer til seg selv innenfor den CTE. Den rekursive CTE er nyttig når du arbeider med hierarkiske data, da CTE fortsetter å kjøre inntil spørringen returnerer hele hierarkiet.

while og do while loop i java

Et typisk eksempel på hierarkiske data er en tabell som inneholder en liste over ansatte. For hver ansatt gir tabellen en referanse til den personens leder. Denne referansen er i seg selv en ansatt-ID i samme tabell. Du kan bruke en rekursiv CTE for å vise hierarkiet av ansattdata.

Hvis en CTE er opprettet feil, kan den gå inn i en uendelig sløyfe. For å forhindre dette kan MAXRECURSION-hintet legges til i OPTION-leddet til den primære SELECT-, INSERT-, UPDATE-, DELETE- eller MERGE-setningen.

En tabell opprettes: 

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)>

Etter at tabellen Employees er opprettet, opprettes følgende SELECT-setning, som innledes med en WITH-klausul som inkluderer en CTE kalt cteReports:

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>

Dermed kan CTE-er være nyttige når du trenger å generere midlertidige resultatsett som kan nås i en SELECT-, INSERT-, UPDATE-, DELETE- eller MERGE-setning.

json filen

Funksjoner og begrensninger for vanlige tabelluttrykk i Azure

Den nåværende implementeringen av CTE-er i Azure Synapse Analytics og Analytics Platform System (PDW) har følgende funksjoner og begrensninger:

  1. En CTE kan bare spesifiseres i en SELECT-setning.
  2. En CTE kan bare spesifiseres i en CREATE VIEW-setning.
  3. En CTE kan bare spesifiseres i en CREATE TABLE AS SELECT (CTAS)-setning.
  4. En CTE kan bare spesifiseres i en CREATE REMOTE TABLE AS SELECT (CRTAS)-setning.
  5. En CTE kan bare spesifiseres i en CREATE EXTERNAL TABLE AS SELECT (CETAS)-setning.