Sammanfattning av Common Table Expressions (CTE)

Hierarkiska och rekursiva frågor i SQL Server

Jasmin Ludolf

Content Developer

Kursöversikt

Vad är rekursion och hur används det?
Hur kan du använda Common Table Expressions (CTE) för hierarkiska och rekursiva frågor?
Hur representerar du hierarki i SQL och hur frågar du mot den?
Verkliga exempel (t.ex. företagsorganisationer, materiallistor, flygrutter)

Bild på ryska dockor och deras rekursiva egenskap.

Vad är en CTE?

Definition av en CTE

Anger en tillfällig namngiven resultatmängd, kallad en common table expression (CTE). Den härleds från en enkel fråga och definieras inom körningsomfånget för ett enskilt uttryck

Struktur för en CTE

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

Användning av CTE:er

Hantera komplexa frågor
Kan användas i SELECT-, INSERT-, UPDATE- eller DELETE-satser
Flera CTE:er kan definieras i en enda WITH-sats
Kombinera flera CTE:er med UNION eller JOIN
Ersätta en vy
Självrefererande tabell
Rekursiv fråga

Definiera en CTE för en IT-organisation

Välj ut chefer med en CTE:

WITH JOBS (id, SurName, JobTitle) AS

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

WITH AS för att definiera CTE:n
SELECT mot tabellen Employee

Definiera en CTE för en IT-organisation

Välj ut anställda med lön över 10 000

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

Definiera en CTE för en IT-organisation

Hela frågan:

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

Nu kör vi en övning!

Hierarkiska och rekursiva frågor i SQL Server