The Typowe wyrażenia tabelowe (CTE) zostały wprowadzone do standardowego języka SQL w celu uproszczenia różnych klas zapytań SQL, dla których tabela pochodna była po prostu nieodpowiednia. CTE zostało wprowadzone w SQL Server 2005, wspólne wyrażenie tabelowe (CTE) to tymczasowy nazwany zestaw wyników, do którego można się odwoływać w obrębie WYBIERAĆ , WSTAWIĆ , AKTUALIZACJA , Lub USUWAĆ oświadczenie. Możesz także użyć CTE w widoku CREATE, jako część zapytania SELECT tego widoku. Ponadto od wersji SQL Server 2008 można dodać CTE do nowej instrukcji MERGE.

Dlaczego potrzebujemy CTE na serwerze SQL?

Wspólna tabela [przestarzała] CTE jest potężna SQL konstrukcja, która pomaga uprościć zapytania. CTE działają jak wirtualne tabele (z rekordami i kolumnami), które są tworzone podczas wykonywania zapytania, wykorzystywane przez zapytanie i usuwane po wykonaniu zapytania.

Korzystanie z CTE

Możemy zdefiniować CTE, dodając klauzulę WITH bezpośrednio przed instrukcją SELECT, INSERT, UPDATE, DELETE lub MERGE. Klauzula WITH może zawierać jeden lub więcej CTE oddzielonych przecinkami.

zestaw c++

Składnia:

[Z [, …]]

::=

nazwa_cte [(nazwa_kolumny [, …])]

AS (cte_query)

ciąg.format

Argument

1. Nazwa wyrażenia: Prawidłowy identyfikator wspólnego wyrażenia tabelowego. Nazwa_wyrażenia musi różnić się od nazw innych typowych wyrażeń tabelowych zdefiniowanych w tej samej klauzuli WITH, ale nazwa_wyrażenia może być taka sama jak nazwa tabeli podstawowej lub widoku. Wszystkie odniesienia do nazwy_wyrażenia w zapytaniu używają wspólnego wyrażenia tabelowego zamiast obiektu podstawowego.
2. Nazwa kolumny: Określa nazwę kolumny we wspólnym wyrażeniu tabelowym. Zduplikowane nazwy w ramach jednej definicji CTE są niedozwolone. Liczba nazw kolumn musi odpowiadać liczbie kolumn w zestawie wyników CTE_query_definition. Lista nazw kolumn jest opcjonalna tylko wtedy, gdy wszystkie kolumny wynikowe w definicji zapytania mają różne nazwy.
3. Definicja zapytania CTE: Określa instrukcję SELECT, której zestaw wyników spełnia typowe wyrażenie tabelowe. Instrukcja SELECT dla CTE_query_defining musi spełniać te same wymagania, co tworzenie widoku, z tą różnicą, że CTE nie może definiować innego CTE. Aby uzyskać więcej informacji, zobacz sekcję Uwagi i UTWÓRZ WIDOK (Transact-SQL). Jeżeli zdefiniowano wiele ustawień CTE_query_settings, definicje zapytań należy połączyć z jednym z operatorów zbiorowych UNION ALL, UNION, EXCEPT lub INTERSECT.
   

Zasady definiowania i używania rekurencyjnych wspólnych wyrażeń tabelowych

Poniższe wytyczne mają zastosowanie do definiowania rekurencyjnych typowych wyrażeń tabelowych:

1. Rekurencyjna definicja CTE musi zawierać co najmniej dwie definicje zapytań CTE, element zakotwiczający i element rekurencyjny. Można zdefiniować wiele elementów zakotwiczonych i rekurencyjnych. Jednakże wszystkie definicje zapytań elementów zakotwiczonych muszą być umieszczone przed pierwszą definicją elementu rekurencyjnego. Wszystkie definicje zapytań CTE są elementami zakotwiczonymi, chyba że odwołują się do samego CTE.
2. Elementy kotwiące muszą być połączone jednym z następujących operatorów mnogościowych: UNION ALL, UNION, INTERSECT, EXCEPT. UNION ALL jest jedynym prawidłowym operatorem mnogościowym pomiędzy ostatnim elementem zakotwiczającym a pierwszym elementem rekurencyjnym w przypadku łączenia wielu elementów rekurencyjnych. Elementy zakotwiczone i rekurencyjne muszą mieć tę samą liczbę kolumn.
3. Typ danych kolumn elementu rekurencyjnego musi być taki sam, jak typ danych odpowiedniej kolumny elementu zakotwiczonego.
4. Następujące elementy nie są dozwolone w jej CTE_query_definition dla elementów rekurencyjnych:

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

Tworzenie rekurencyjnego wspólnego wyrażenia tabelowego

Rekurencyjny CTE to taki, który odwołuje się do samego siebie w tym CTE. Rekurencyjne CTE jest przydatne podczas pracy z danymi hierarchicznymi, ponieważ CTE jest kontynuowane, dopóki zapytanie nie zwróci całej hierarchii.

Typowym przykładem danych hierarchicznych jest tabela zawierająca listę pracowników. Dla każdego pracownika tabela zawiera odniesienie do jego przełożonego. Odniesienie to samo w sobie jest identyfikatorem pracownika w tej samej tabeli. Możesz użyć rekurencyjnego CTE, aby wyświetlić hierarchię danych pracowników.

Jeśli CTE zostanie utworzone nieprawidłowo, może wejść w nieskończoną pętlę. Aby temu zapobiec, w klauzuli OPTION podstawowej instrukcji SELECT, INSERT, UPDATE, DELETE lub MERGE można dodać wskazówkę MAXRECURSION.

Inkscape vs Gimp

Tworzona jest tabela:

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 utworzeniu tabeli Pracownicy tworzona jest następująca instrukcja SELECT, poprzedzona klauzulą ​​WITH zawierającą CTE o nazwie 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>

Zatem CTE mogą być przydatne, gdy trzeba wygenerować tymczasowe zestawy wyników, do których można uzyskać dostęp za pomocą instrukcji SELECT, INSERT, UPDATE, DELETE lub MERGE.

Funkcje i ograniczenia typowych wyrażeń tabelowych na platformie Azure

Bieżąca implementacja współczynników CTE w systemie Azure Synapse Analytics i Analytics Platform System (PDW) ma następujące funkcje i ograniczenia:

1. CTE można określić tylko w instrukcji SELECT.
2. CTE można określić tylko w instrukcji CREATE VIEW.
3. Wartość CTE można określić wyłącznie w instrukcji CREATE TABLE AS SELECT (CTAS).
4. Wartość CTE można określić wyłącznie w instrukcji CREATE REMOTE TABLE AS SELECT (CRTAS).
5. Wartość CTE można określić wyłącznie w instrukcji CREATE EXTERNAL TABLE AS SELECT (CETAS).