MS SQL wyrażenia CTE z przykładami zastosowania
Szybki kontakt




Zastosowanie wspólnych wyrażeń tablicowych CTE

Wyrażenia CTE (Common Table Expressions) zostały wprowadzone od wersji MS SQL Server 2005. Są to instrukcje języka SQL, które pozwalają uprościć podzapytania. Informacje dotyczące pozapytań znajdziesz na naszej stronie pod linkiem MS SQL podzapytania.

Zastosowanie CTE poprawia czytelność kodu.

Wyrażenie CTE generuje tymczasową tabelę w pamięci, do której można się odwołać w ramach innych instrukcji tj. SELECT, INSERT, UPDATE i DELETE. Do wyniku zwróconego przez CTE odwołujemy się na tych samych zasadach, co do tabel w bazie danych.

Przykłady przedstawione w artykule możesz uruchomić w bazach AdventureWorks i Northwind.

Tworzenie wyrażenia CTE

Definicję CTE otwiera słowo kluczowe WITH. Za słowem WITH umieszczamy naszą nazwę dla zbioru, który zostanie utworzony za pomocą wyrażenia CTE.

CTE - tworzenie wyrażenia

Zapytanie z wykorzystaniem CTE musi składać się z dwóch części:

  • pierwsza część definiuje tabelę tymczasową
  • druga część odwołuje się do tej tabeli

Instrukcja CTE tworzy zbiór danych na wzór tabeli. Zbiór ten musi mieć nazwane wszystkie kolumny i nazwy muszą być unikalne. Możemy je nadać na dwa sposoby:

  • w „ciele” zawartego zapytania przez aliasy:
    Use AdventureWorks2017;
    WITH Sales_CTE 
    AS
    (   
        SELECT
          ProductID,
          SUM(OrderQty) as Liczba_zamowien,
          SUM(OrderQty*UnitPrice) as Wartosc_Zamowienia
        FROM Sales.SalesOrderDetail
        GROUP BY ProductID
    )
    SELECT * from Sales_CTE;
    

    Nazwy kolumn pochodzą tutaj z zapytania znajdującego się w linijkach 6-8.

    Wynik zapytania:

    Wyrażenie CTE tworzenie - kolumny w ciele wyrażenia CTE
  • jeśli nazw nie chcemy nadać aliasami, możemy jest nadać w klauzuli WITH – umieszczamy je w nawiasie za nazwą zbioru
    Use AdventureWorks2017;
    WITH Sales_CTE (ProductID, Zamowienia, Wartosc)
    AS
    (   
        SELECT
          ProductID,
          SUM(OrderQty) as Liczba_zamowien,
          SUM(OrderQty*UnitPrice) as Wartosc_Zamowienia
        FROM Sales.SalesOrderDetail
        GROUP BY ProductID
    )
    SELECT * from Sales_CTE;
    
    

Wynik zapytania:

Wyrażenie CTE tworzenie - kolumny w klauzuli WITH

Nazwom kolumn w wyniku zapytania zostały nadane te nazwy, które wymienione zostały w klauzuli WITH.

Jeśli podamy nazwy w „ciele” wyrażenia tablicowego, nie musimy ich określać w klauzuli WITH.

Przykłady wyrażeń CTE

Do zbioru danych utworzonego za pomocą wyrażenia CTE możemy odwoływać się w następujących później klauzulach SELECT, INSERT, UPDATE lub DELETE, które stają się częściową wyrażenia CTE.

Klauzula SELECT z wyrażeniem CTE

Przykład 1

Use northwind;
WITH Employee_CTE 
AS
(SELECT LastName,
        FirstName,
        HireDate
 FROM   Employees)


SELECT LastName,
       FirstName
FROM   Employee_CTE;

Część pierwsza zapytania (linijki od 2 do 7) to pierwsza część wyrażenia CTE, które jest definicją tabeli tymczasowej w pamięci.
Kod w linijkach 4-7 można zaznaczyć i uruchomić niezależnie od pozostałej części zapytania.

W przykładzie:

  • w linijkach 4-7 odczytujemy trzy kolumny LastName, FirstName, HireDate z tabeli Employees
  • w linii 1 definiujemy CTE za pomocą wyrażenia WITH – nadajemy mu nazwę „Employee_CTE” i definiujemy jego kolumny. Mogą tutaj być wymienione inne nazwy dla kolumn, które zostały wskazane w linijkach 4-6.
  • w linijkach 10-12 mamy oddzielne zapytanie, za pomocą którego odczytujemy dane z wyrażenia CTE
  • całe zapytanie kończymy średnikiem

Przykład 2

Use northwind;
WITH Employee_CTE (LastName, FirstName, HireDate)
AS
(SELECT LastName,
        FirstName,
	HireDate
 FROM   Employees)


SELECT LastName,
       FirstName
FROM   Employee_CTE;

Część pierwsza zapytania (linijki od 2 do 7) to wyrażenie CTE, które jest definicją zapytania CTE.
Kod w linijkach 4-7 można zaznaczyć i uruchomić niezależnie od pozostałej części zapytania.

W przykładzie:

  • w linijkach 4-7 odczytujemy trzy kolumny LastName, FirstName, HireDate z tabeli Employees
  • w linii 2 definiujemy CTE za pomocą wyrażenia WITH – nadajemy mu nazwę „Employee_CTE” i definiujemy jego kolumny. Muszą tutaj być wymienione te same kolumny, które zostały wskazane w linijkach 4-6, ale mogą mieć one inne nazwy. W wyniku zapytania będą wyświetlone te nazwy kolumn, które zostały podane w klauzuli WITH
  • w linijkach 10-12 mamy drugą część zapytania, za pomocą której odczytujemy dane
  • całe zapytanie kończymy średnikiem

Przykład 3

W klauzuli WITH możemy zdefiniować więcej niż jedno zapytanie CTE.

Use AdventureWorks2017;
WITH 

Employee_CTE (LastName, FirstName, HireDate, EmployeeID)
AS
(SELECT LastName,
FirstName,
HireDate,
EmployeeID
FROM Employees),
 
Orders_CTE (EmployeeID, CustomerID, Freight)
AS 
(SELECT
EmployeeID,
CustomerID,
Freight
FROM Orders
WHERE Freight > 100)

SELECT LastName,
HireDate,
Freight
FROM Employee_CTE a
JOIN Orders_CTE b
on a.EmployeeID=b.EmployeeID

W przykładzie tym:

  • w linijce drugiej umieszczamy instrukcję WITH, za pomocą której tworzymy zestaw danych Employee_CTE
  • w linijkach 4-10 tworzymy pierwszy zestaw danych o nazwie Employee_CTE. Wyrażenie to będzie zawierało dane z kolumn LastName, FirstName, HireDate i EmployeeID z tabeli Employees
  • w linijkach 12-19 tworzymy drugi zestaw danych o nazwie Orders_CTE. Wyrażenie to będzie zawierało dane z kolumn EmployeeID, CustomerID, Freight z tabeli Orders i tylko te rekordy, w przypadku których wartość w kolumnie Freight jest większa od 100.
  • w linijkach 21-26 tworzymy zapytanie, za pomocą którego odwołujemy się do obydwu zestawów danych tj. do Employee_CTE i Orders_CTE. Zestawy danych CTE należy połączyć ze sobą na takiej samej zasadzie, na jakiej łączy się tabele. Informacje dotyczące łączenia tabel znajdziesz na naszej stronie pod linkiem MS SQL złączenia tabel
Klauzula SELECT INTO z wyrażeniem CTE

W poniższym przykładzie tworzymy wyrażenie CTE o nazwie Employee_CTE. Następnie piszemy zapytanie, za pomocą którego:

  • odczytujemy zbiór danych Employee_CTE utworzony wewnątrz CTE
  • wybieramy z niego tylko te osoby, których nazwisko (LastName) zaczyna się na literę „K”
  • „wrzucamy” wybrane osoby do nowej tabeli o nazwie „Nowi_pracownicy”
WITH Employee_CTE (LastName, FirstName, HireDate)
AS
(SELECT LastName,
        FirstName,
		HireDate
 FROM   Employees)


SELECT FirstName, LastName 
INTO Nowi_pracownicy
FROM   Employee_CTE
WHERE LastName LIKE 'K%';
Klauzula INSERT INTO … SELECT z wyrażeniem CTE

W poniższym przykładzie tworzymy wyrażenie CTE o nazwie Employee_CTE. Następnie piszemy zapytanie, za pomocą którego:

  • odczytujemy zbiór danych Employee_CTE utworzony wewnątrz CTE
  • wybieramy z niego tylko te osoby, których nazwisko (LastName) zaczyna się na literę „S”
  • „wrzucamy” wybrane osoby do istniejącej tabeli o nazwie „Nowi_pracownicy”
WITH Employee_CTE (LastName, FirstName, HireDate)
AS
(SELECT LastName,
        FirstName,
	HireDate
 FROM   Employees)

INSERT INTO Nowi_pracownicy
SELECT
FirstName, LastName
FROM   Employee_CTE
WHERE LastName LIKE 'S%';
Klauzula UPDATE z wyrażeniem CTE

W poniższym przykładzie tworzymy wyrażenie CTE o nazwie Employee_CTE. Następnie piszemy zapytanie, za pomocą którego dla każdej osoby, która ma na nazwisko „King” zmieniamy nazwisko na „Mordka”.

WITH Employee_CTE (LastName, FirstName, HireDate)
AS
(SELECT LastName,
        FirstName,
	HireDate
 FROM   Employees)

UPDATE Employee_CTE
SET LastName = 'Mordka'
FROM   Employee_CTE
WHERE LastName = 'King';

Wyrażenia CTE to konstrukcje języka SQL, które mają właściwość odczytu danych z tabeli i zapisu danych do tabeli. Dlatego, jeśli utworzymy wyrażenie CTE dla danej tabeli i odwołamy się do niego za pomocą klauzuli UPDATE lub DELETE, zaktualizowane lub usunięte zostaną dane z tabeli, dla której utworzone zostało CTE.

W przykładzie zaktualizowane zostały dane w tabeli Employees. Poniżej odczytujemy dane z tabeli Employees, aby sprawdzić zmiany wprowadzone w tabeli zapytaniem UPDATE:

USE northwind;
SELECT * 
FROM Employees;
Wyrażenie CTE z klauzulą UPDATE
Klauzula DELETE z wyrażeniem CTE

W poniższym przykładzie tworzymy wyrażenie CTE o nazwie Employee_CTE. Następnie piszemy zapytanie, za pomocą którego usuniemy wszystkie osoby, które mają na nazwisko „Mordka”.

WITH Employee_CTE (LastName, FirstName, HireDate)
AS
(SELECT LastName,
        FirstName,
	HireDate
 FROM   Employees)

DELETE FROM Employee_CTE
WHERE LastName = 'Mordka';

Tradycyjnie prosimy o komentarz lub wiadomość do nas, jeśli jakiś obszar wyrażeń CTE nie jest opisany lub wymaga doprecyzowania.

Dodaj komentarz

Twój adres email nie zostanie opublikowany.