Data: 13.07.2021

Wyrażenia CTE w MS SQL Server

MS SQL wyrażenia CTE

Spis treści

Wyrażenia CTE (wspólne wyrażenia tablicowe) umożliwiają uproszczenie podzapytań i poprawę czytelności kodu.

MS SQL wyrażenia CTE – zastosowanie

Wyrażenia CTE (ang. 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 podzapytań znajdziesz na naszej stronie pod linkiem MS SQL podzapytania.

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.

Title

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_zam,
          SUM(OrderQty*UnitPrice) as Wart_zam
        FROM Sales.SalesOrderDetail
        GROUP BY ProductID
    )
    SELECT * from Sales_CTE;

    Wynik zapytania:

    Title

  • 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),
          SUM(OrderQty*UnitPrice)
        FROM Sales.SalesOrderDetail
        GROUP BY ProductID
    )
    SELECT * from Sales_CTE;

    Wynik zapytania:

    Title

    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ęścią 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;

Title

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';

Zapraszamy na nasz kurs SQL Server poziom średnio-zaawansowany, na którym przećwiczysz wyrażenia CTE oraz inne elementy języka MS SQL.

Jarek Olechno
Jarek Olechno
Programista SQL, Cloud Developer
Udostępnij wpis:udostępnij Facebookudostępnij Linkedinudostępnij e-mail

Polecane

Inne w tej kategorii

Instrukcja CASE w SQL Server – przewodnik z przykładamiFunkcje matematyczne w SQL Server – praca na liczbachJak zacząć pracę z SQL Server Management Studio?Tworzenie funkcji skalarnych (UDF) w SQL ServerTworzenie funkcji tabelarycznych (UDF) w SQL ServerTypy danych MS SQL Server - INT, VARCHAR, DATETIME i inneTworzenie i modyfikowanie tabel w MS SQL ServerTabele tymczasowe w MS SQL Server

Ostatnie wpisy

uv – szybszy pip, poetry i pyenv w jednym narzędziu PythonCopilot w MS Teams - jak działa i jak z niego korzystać?Microsoft 365 Copilot - kompletny przewodnikCopilot w Outlooku - jak używać AI w poczcie?Prompty AI do tworzenia tekstów – jak je dobrze pisać?Instalacja bibliotek Pythona w środowisku MinicondaImport danych z Excela do Power BIJak tworzyć miary w Power BI?