De Vanliga tabelluttryck (CTE) introducerades i standard SQL för att förenkla olika klasser av SQL-frågor för vilka en härledd tabell bara var olämplig. CTE introducerades i SQL Server 2005, det gemensamma tabelluttrycket (CTE) är en temporär namngiven resultatuppsättning som du kan referera till inom en VÄLJ , FÖRA IN , UPPDATERING , eller RADERA påstående. Du kan också använda en CTE i en CREATE-vy, som en del av vyns SELECT-fråga. Dessutom, från och med SQL Server 2008, kan du lägga till en CTE till den nya MERGE-satsen.

Varför behöver vi CTE i SQL-servern?

En vanlig tabell [utfasad] CTE är en kraftfull SQL konstruktion som hjälper till att förenkla frågor. CTE:er fungerar som virtuella tabeller (med poster och kolumner) som skapas under körning av en fråga, används av frågan och raderas efter att frågan har körts.

Använder CTE

Vi kan definiera CTE genom att lägga till en WITH-sats direkt före SELECT-, INSERT-, UPDATE-, DELETE- eller MERGE-satsen. WITH-satsen kan inkludera en eller flera CTE:er separerade med kommatecken.

spara youtube video vlc

Syntax:

[Med [, …]]

::=

cte_name [(kolumnnamn [, …])]

AS (cte_query)

Argument

1. Uttryckets namn: En giltig identifierare för ett vanligt tabelluttryck. Uttrycksnamn måste skilja sig från namnen på andra vanliga tabelluttryck som definieras i samma WITH-sats, men uttrycksnamn kan vara detsamma som namnet på bastabellen eller vyn. Alla referenser till expression_name i frågan använder det gemensamma tabelluttrycket istället för basobjektet.
2. Kolumnnamn: Anger ett kolumnnamn i ett vanligt tabelluttryck. Dubblettnamn inom en enda CTE-definition är inte tillåtna. Antalet kolumnnamn måste matcha antalet kolumner i CTE_query_definitionens resultatuppsättning. En lista med kolumnnamn är valfri endast om alla resulterande kolumner i frågedefinitionen har olika namn.
3. CTE_QueryDefinition: Anger en SELECT-sats vars resultatuppsättning uppfyller ett vanligt tabelluttryck. SELECT-satsen för CTE_query_definiing måste uppfylla samma krav som att skapa en vy, förutom att en CTE inte kan definiera en annan CTE. För mer information, se avsnittet Anmärkningar och SKAPA VY (Transact-SQL). Om flera CTE_query_settings är definierade måste frågedefinitionerna kombineras med en av setoperatorerna UNION ALL, UNION, EXCEPT eller INTERSECT.
   

Regler för definition och användning av rekursiva vanliga tabelluttryck

Följande riktlinjer gäller för att definiera rekursiva vanliga tabelluttryck:

hur man får ett aktuellt datum i java

1. En rekursiv CTE-definition måste innehålla minst två CTE-frågedefinitioner, en ankarmedlem och en rekursiv medlem. Du kan definiera flera ankare och rekursiva medlemmar. Alla ankarmedlemsfrågadefinitioner måste dock placeras före den första rekursiva medlemsdefinitionen. Alla CTE-frågedefinitioner är ankarmedlemmar såvida de inte refererar till själva CTE.
2. Ankarelement måste kombineras med en av följande uppsättningsoperatorer: UNION ALL, UNION, INTERSECT, EXCEPT. UNION ALL är den enda giltiga setoperatorn mellan den sista ankarmedlemmen och den första rekursiva medlemmen när flera rekursiva medlemmar sammanfogas. Ankare och rekursiva element måste ha samma antal kolumner.
3. Datatypen för den rekursiva medlemmens kolumner måste vara densamma som datatypen för motsvarande kolumn för ankarelementet.
4. Följande objekt är inte tillåtna i hennes CTE_query_definition för rekursiva medlemmar:

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

Skapa ett rekursivt gemensamt tabelluttryck

En rekursiv CTE är en som refererar till sig själv inom den CTE. Den rekursiva CTE är användbar när du arbetar med hierarkiska data eftersom CTE fortsätter att exekvera tills frågan returnerar hela hierarkin.

Ett typiskt exempel på hierarkisk data är en tabell som innehåller en lista över anställda. För varje anställd ger tabellen en referens till den personens chef. Den referensen är i sig ett anställd-ID i samma tabell. Du kan använda en rekursiv CTE för att visa hierarkin av personaldata.

vad är min skärmstorlek

Om en CTE skapas felaktigt kan den gå in i en oändlig loop. För att förhindra detta kan MAXRECURSION-tipset läggas till i OPTION-satsen i den primära SELECT-, INSERT-, UPDATE-, DELETE- eller MERGE-satsen.

En tabell skapas:

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 att tabellen Employees har skapats skapas följande SELECT-sats, som föregås av en WITH-sats som innehåller en CTE med namnet 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 vara användbara när du behöver generera tillfälliga resultatuppsättningar som kan nås i en SELECT-, INSERT-, UPDATE-, DELETE- eller MERGE-sats.

Funktioner och begränsningar för vanliga tabelluttryck i Azure

Den nuvarande implementeringen av CTE:er i Azure Synapse Analytics och Analytics Platform System (PDW) har följande funktioner och begränsningar:

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