Riepilogo delle Common Table Expressions (CTE)

Query gerarchiche e ricorsive in SQL Server

Jasmin Ludolf

Content Developer

Struttura del corso

Cos'è la ricorsione e come usarla?
Come usare le Common Table Expressions (CTE) per query gerarchiche e ricorsive?
Come rappresentare una gerarchia in SQL e come interrogarla?
Esempi reali (es. organigrammi aziendali, distinte base, piano di volo)

Immagine di bambole russe e la loro proprietà ricorsiva.

Cos'è una CTE?

Definizione di una CTE

Specifica un risultato temporaneo con nome, detto common table expression (CTE). È ricavato da una query semplice e definito nello scope di esecuzione di una singola istruzione

Struttura di una CTE

WITH CTEtable as 
(
    -- a query --
)

SELECT *
    FROM CTEtable

Uso delle CTE

Gestire query complesse
Usabile in SELECT, INSERT, UPDATE o DELETE
Più CTE in un unico WITH
Combina più CTE con UNION o JOIN
Alternativa a una vista
Autoriferimento di una tabella
Query ricorsive

Definisci una CTE per un'organizzazione IT

Seleziona i manager con una CTE:

WITH JOBS (id, SurName, JobTitle) AS

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

WITH AS per definire la CTE
SELECT sulla tabella Employee

Definisci una CTE per un'organizzazione IT

Seleziona dipendenti con stipendio oltre 10.000

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

Definisci una CTE per un'organizzazione IT

L'intera query:

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

Passiamo alla pratica!

Query gerarchiche e ricorsive in SQL Server