Přehled Common Table Expressions (CTE)

Hierarchical and Recursive Queries in SQL Server

Jasmin Ludolf

Content Developer

Přehled kurzu

Co je rekurze a jak ji používat?
Jak lze využít Common Table Expressions (CTE) pro hierarchické a rekurzivní dotazy?
Jak reprezentovat hierarchii v SQL a jak ji dotazovat?
Příklady z praxe (např. organizační struktura, kusovník, letový plán)

Obrázek ruských matrjošek a jejich rekurzivní vlastnosti.

Co je CTE?

Definice CTE

Určuje dočasnou pojmenovanou sadu výsledků, označovanou jako common table expression (CTE). Vychází z jednoduchého dotazu a je definována v rámci rozsahu provádění jednoho příkazu

Struktura CTE

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

Použití CTE

Správa složitých dotazů
Lze použít v příkazech SELECT, INSERT, UPDATE nebo DELETE
Více CTE lze definovat v jednom příkazu WITH
Kombinace více CTE pomocí UNION nebo JOIN
Náhrada za pohled (view)
Sebereference tabulky
Rekurzivní dotaz

Definice CTE pro IT organizaci

Výběr manažerů pomocí CTE:

WITH JOBS (id, SurName, JobTitle) AS

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

WITH AS pro definici CTE
SELECT nad tabulkou Employee

Definice CTE pro IT organizaci

Výběr zaměstnanců s platem nad 10 000

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

Definice CTE pro IT organizaci

Celý dotaz:

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

Vyzkoušejte si to!

Hierarchical and Recursive Queries in SQL Server