The Společné tabulkové výrazy (CTE) byly zavedeny do standardního SQL za účelem zjednodušení různých tříd SQL dotazů, pro které byla odvozená tabulka právě nevhodná. CTE byl představen v SQL Server 2005, společný tabulkový výraz (CTE) je dočasná pojmenovaná sada výsledků, na kterou můžete odkazovat v rámci VYBRAT , VLOŽIT , AKTUALIZACE nebo VYMAZAT prohlášení. CTE můžete také použít v pohledu CREATE jako součást dotazu SELECT pohledu. Navíc od SQL Server 2008 můžete přidat CTE do nového příkazu MERGE.

Proč potřebujeme CTE na SQL serveru?

Běžná tabulka [zastaralé]CTE je mocná SQL konstrukce, která pomáhá zjednodušit dotazy. CTE fungují jako virtuální tabulky (se záznamy a sloupci), které se vytvářejí během provádění dotazu, používají je dotaz a po provedení dotazu se vymažou.

Pomocí CTE

CTE můžeme definovat přidáním klauzule WITH přímo před příkazy SELECT, INSERT, UPDATE, DELETE nebo MERGE. Klauzule WITH může obsahovat jeden nebo více CTE oddělených čárkami.

znak na řetězec v jazyce Java

Syntax:

[S [, …]]

::=

cte_name [(název_sloupce [, …])]

AS (cte_query)

Argument

1. Název výrazu: Platný identifikátor pro běžný tabulkový výraz. Název_výrazu se musí lišit od názvů jiných běžných tabulkových výrazů definovaných ve stejné klauzuli WITH, ale název_výrazu může být stejný jako název základní tabulky nebo pohledu. Všechny odkazy na název_výrazu v dotazu používají místo základního objektu společný tabulkový výraz.
2. Název sloupce: Určuje název sloupce v běžném tabulkovém výrazu. Duplicitní názvy v rámci jedné definice CTE nejsou povoleny. Počet názvů sloupců se musí shodovat s počtem sloupců v sadě výsledků CTE_query_definition. Seznam názvů sloupců je volitelný pouze v případě, že všechny výsledné sloupce v definici dotazu mají různé názvy.
3. CTE_QueryDefinition: Určuje příkaz SELECT, jehož výsledná sada vyhovuje běžnému tabulkovému výrazu. Příkaz SELECT pro CTE_query_defining musí splňovat stejné požadavky jako při vytváření pohledu, kromě toho, že CTE nemůže definovat jiný CTE. Další informace naleznete v části Poznámky a CREATE VIEW (Transact-SQL). Pokud je definováno více CTE_query_settings, musí být definice dotazu zkombinovány s jedním z operátorů sady UNION ALL, UNION, EXCEPT nebo INTERSECT.
   

Pravidla pro definici a použití rekurzivních společných tabulkových výrazů

Následující pokyny platí pro definování rekurzivních běžných tabulkových výrazů:

řetězec json java

1. Rekurzivní definice CTE musí obsahovat alespoň dvě definice dotazu CTE, kotevní člen a rekurzivní člen. Můžete definovat více kotevních a rekurzivních členů. Všechny definice dotazu na kotevní člen však musí být umístěny před první definicí rekurzivního člena. Všechny definice dotazů CTE jsou členy kotvy, pokud neodkazují na samotný CTE.
2. Kotevní prvky musí být kombinovány s jedním z následujících operátorů sady: UNION ALL, UNION, INTERSECT, EXCEPT. UNION ALL je jediný platný operátor množiny mezi posledním kotevním členem a prvním rekurzivním členem při spojování více rekurzivních členů. Kotevní a rekurzivní prvky musí mít stejný počet sloupců.
3. Datový typ sloupců rekurzivního členu musí být stejný jako datový typ odpovídajícího sloupce kotevního členu.
4. V její definici CTE_query_definition pro rekurzivní členy nejsou povoleny následující položky:

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

Vytvoření rekurzivního společného tabulkového výrazu

Rekurzivní CTE je takový, který odkazuje sám na sebe v rámci tohoto CTE. Rekurzivní CTE je užitečné při práci s hierarchickými daty, protože CTE pokračuje v provádění, dokud dotaz nevrátí celou hierarchii.

Typickým příkladem hierarchických dat je tabulka, která obsahuje seznam zaměstnanců. U každého zaměstnance tabulka poskytuje odkaz na manažera dané osoby. Tento odkaz je sám o sobě ID zaměstnance ve stejné tabulce. K zobrazení hierarchie dat zaměstnanců můžete použít rekurzivní CTE.

java pole seřazeno

Pokud je CTE vytvořen nesprávně, může vstoupit do nekonečné smyčky. Chcete-li tomu zabránit, můžete do klauzule OPTION primárního příkazu SELECT, INSERT, UPDATE, DELETE nebo MERGE přidat nápovědu MAXRECURSION.

Vytvoří se tabulka:

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

Po vytvoření tabulky Zaměstnanci se vytvoří následující příkaz SELECT, kterému předchází klauzule WITH, která obsahuje CTE s názvem 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>

CTE tedy mohou být užitečné, když potřebujete generovat dočasné sady výsledků, ke kterým lze přistupovat pomocí příkazu SELECT, INSERT, UPDATE, DELETE nebo MERGE.

Funkce a omezení běžných tabulkových výrazů v Azure

Aktuální implementace CTE v Azure Synapse Analytics a Analytics Platform System (PDW) má následující funkce a omezení:

1. CTE lze zadat pouze v příkazu SELECT.
2. CTE lze zadat pouze v příkazu CREATE VIEW.
3. CTE lze zadat pouze v příkazu CREATE TABLE AS SELECT (CTAS).
4. CTE lze zadat pouze v příkazu CREATE REMOTE TABLE AS SELECT (CRTAS).
5. CTE lze zadat pouze v příkazu CREATE EXTERNAL TABLE AS SELECT (CETAS).