logo

TechCodeview

CTE i SQL

Det Almindelige tabeludtryk (CTE) blev introduceret i standard SQL for at forenkle forskellige klasser af SQL-forespørgsler, som en afledt tabel bare var uegnet til. CTE blev introduceret i SQL Server 2005, det fælles tabeludtryk (CTE) er et midlertidigt navngivet resultatsæt, som du kan referere til i en VÆLG , INDSÆT , OPDATERING , eller SLET udmelding. Du kan også bruge en CTE i en CREATE-visning som en del af visningens SELECT-forespørgsel. Fra og med SQL Server 2008 kan du desuden tilføje en CTE til den nye MERGE-sætning.

Hvorfor har vi brug for CTE i SQL-serveren?

En almindelig tabel [forældet] CTE er en kraftfuld SQL konstruktion, der hjælper med at forenkle forespørgsler. CTE'er fungerer som virtuelle tabeller (med poster og kolonner), der oprettes under udførelse af forespørgsler, bruges af forespørgslen og slettes, efter at forespørgslen er eksekveret.

Brug af CTE

Vi kan definere CTE'er ved at tilføje en WITH-sætning direkte før SELECT-, INSERT-, UPDATE-, DELETE- eller MERGE-sætningen. WITH-sætningen kan inkludere en eller flere CTE'er adskilt af kommaer.



registrere hukommelse

Syntaks:

[Med [, …]]

::=

cte_name [(kolonne_navn [, …])]

AS (cte_query)

Argument

  1. Udtryksnavn: En gyldig identifikator for et almindeligt tabeludtryk. Udtrykket_navn skal være forskelligt fra navnene på andre almindelige tabeludtryk, der er defineret i det samme WITH-udtryk, men udtryksnavnet kan være det samme som navnet på basistabellen eller visningen. Alle referencer til udtryksnavn i forespørgslen bruger det fælles tabeludtryk i stedet for basisobjektet.
  2. Kolonnenavn: Angiver et kolonnenavn i et almindeligt tabeludtryk. Duplikerede navne inden for en enkelt CTE-definition er ikke tilladt. Antallet af kolonnenavne skal svare til antallet af kolonner i CTE_query_definition's resultatsæt. En liste over kolonnenavne er kun valgfri, hvis alle de resulterende kolonner i forespørgselsdefinitionen har forskellige navne.
  3. CTE_QueryDefinition: Angiver en SELECT-sætning, hvis resultatsæt opfylder et almindeligt tabeludtryk. SELECT-sætningen for CTE_query_defining skal opfylde de samme krav som at oprette en visning, bortset fra at en CTE ikke kan definere en anden CTE. For mere information, se afsnittet Bemærkninger og CREATE VIEW (Transact-SQL). Hvis der er defineret flere CTE_query_settings, skal forespørgselsdefinitionerne kombineres med en af ​​sætoperatorerne UNION ALL, UNION, EXCEPT eller INTERSECT.

Regler for definition og brug af rekursive almindelige tabeludtryk

Følgende retningslinjer gælder for definition af rekursive almindelige tabeludtryk:

  1. En rekursiv CTE-definition skal indeholde mindst to CTE-forespørgselsdefinitioner, et ankermedlem og et rekursivt medlem. Du kan definere flere anker- og rekursive medlemmer. Alle ankermedlemsforespørgselsdefinitioner skal dog placeres før den første rekursive medlemsdefinition. Alle CTE-forespørgselsdefinitioner er ankermedlemmer, medmindre de refererer til selve CTE.
  2. Ankerelementer skal kombineres med en af ​​følgende sæt operatorer: UNION ALL, UNION, INTERSECT, EXCEPT. UNION ALL er den eneste gyldige sætoperator mellem det sidste ankerelement og det første rekursive medlem, når der samles flere rekursive medlemmer. Anker og rekursive elementer skal have samme antal kolonner.
  3. Datatypen for det rekursive elements kolonner skal være den samme som datatypen for den tilsvarende kolonne i ankerelementet.
  4. Følgende elementer er ikke tilladt i hendes 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.>

Oprettelse af et rekursivt fælles tabeludtryk

En rekursiv CTE er en, der refererer til sig selv inden for den CTE. Den rekursive CTE er nyttig, når der arbejdes med hierarkiske data, da CTE fortsætter med at udføre, indtil forespørgslen returnerer hele hierarkiet.

java-array til listen

Et typisk eksempel på hierarkiske data er en tabel, der indeholder en liste over medarbejdere. For hver medarbejder giver tabellen en reference til den pågældende persons leder. Denne reference er i sig selv et medarbejder-id i samme tabel. Du kan bruge en rekursiv CTE til at vise hierarkiet af medarbejderdata.

Hvis en CTE er oprettet forkert, kan den gå ind i en uendelig løkke. For at forhindre dette kan MAXRECURSION-hintet tilføjes i OPTION-udtrykket i den primære SELECT-, INSERT-, UPDATE-, DELETE- eller MERGE-sætning.

Der oprettes en tabel: 

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

Efter at tabellen Employees er oprettet, oprettes følgende SELECT-sætning, som er indledt af en WITH-klausul, der indeholder en CTE ved navn 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>

Således kan CTE'er være nyttige, når du skal generere midlertidige resultatsæt, der kan tilgås i en SELECT-, INSERT-, UPDATE-, DELETE- eller MERGE-sætning.

json filen

Funktioner og begrænsninger for almindelige tabeludtryk i Azure

Den aktuelle implementering af CTE'er i Azure Synapse Analytics og Analytics Platform System (PDW) har følgende funktioner og begrænsninger:

  1. En CTE kan kun angives i en SELECT-sætning.
  2. En CTE kan kun angives i en CREATE VIEW-sætning.
  3. En CTE kan kun angives i en CREATE TABLE AS SELECT (CTAS)-sætning.
  4. En CTE kan kun angives i en CREATE REMOTE TABLE AS SELECT (CRTAS)-sætning.
  5. En CTE kan kun angives i en CREATE EXTERNAL TABLE AS SELECT (CETAS)-sætning.