Podsumowanie wspólnych wyrażeń tabelarycznych (CTE)

Hierarchiczne i rekurencyjne zapytania w SQL Server

Jasmin Ludolf

Content Developer

Plan kursu

Czym jest rekurencja i jak jej używać?
Jak używać wspólnych wyrażeń tabelarycznych (CTE) w zapytaniach hierarchicznych i rekurencyjnych?
Jak reprezentować hierarchię w SQL i jak ją odpytywać?
Przykłady z praktyki (np. struktury organizacyjne, listy materiałów, plany lotów)

Obraz rosyjskich matrioszek ilustrujący właściwość rekurencji.

Czym jest CTE?

Definicja CTE

Określa tymczasowy, nazwany zestaw wyników, znany jako wspólne wyrażenie tabelaryczne (CTE). Jest ono tworzone na podstawie prostego zapytania i definiowane w zakresie wykonania pojedynczej instrukcji

Struktura CTE

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

Zastosowania CTE

Upraszczanie złożonych zapytań
Można używać w instrukcjach SELECT, INSERT, UPDATE lub DELETE
Kilka CTE można zdefiniować w jednej instrukcji WITH
Łączenie wielu CTE za pomocą UNION lub JOIN
Zastępstwo dla widoku
Samo-odwołanie do tabeli
Zapytania rekurencyjne

Definiowanie CTE dla organizacji IT

Wybieranie menedżerów za pomocą CTE:

WITH JOBS (id, SurName, JobTitle) AS

(
    SELECT 
          ID, 
          Name,
          Position
    FROM employee
    WHERE Position like '%Manager%'
),

WITH AS do zdefiniowania CTE
SELECT na tabeli Employee

Definiowanie CTE dla organizacji IT

Wybieranie pracowników z wynagrodzeniem powyżej 10 000

SALARIES (ID,Salary) AS (
   SELECT
        ID,
        Salary
   FROM ITSalary
   WHERE Salary > 10000)

Definiowanie CTE dla organizacji IT

Całe zapytanie:

WITH JOBS (id, SurName, JobTitle) AS 
(    SELECT ID, Name, Position
        FROM employee 
        WHERE Position like '%Manager%'),

SALARIES (ID,Salary) AS
(    SELECT ID, Salary
        FROM ITSalary 
        WHERE Salary > 10000)

SELECT JOBS.Name, JOBS.Position, SALARIES.Salary
    FROM JOBS
    INNER JOIN SALARIES
    on JOBS.ID = SALARIES.ID;

SurNmames     | JobTitle          | Salary
Paul Smith    | IT Manager        | 15,000
Adam Peterson | Sourcing Manager  | 12,500
Anna Nilson   | Portfolio Manager | 10,500

Czas na ćwiczenia!

Hierarchiczne i rekurencyjne zapytania w SQL Server