SQL Academy · Lezione

CTE ricorsive per le gerarchie

Attraversi dati gerarchici, come organigrammi, commenti concatenati e grafi, con WITH RECURSIVE e condizioni di arresto

Lezione 4 di 413 passaggi

CTE ricorsive per le gerarchie è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 4 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.

Perché la ricorsione?

L'SQL semplice non può percorrere un albero di profondità sconosciuta: genitori dei genitori, figli dei figli. Le CTE ricorsive sono la soluzione SQL standard.

Struttura

Una CTE ricorsiva ha due parti unite da UNION ALL:

WITH RECURSIVE name AS (
  -- 1. Anchor query: seed rows
  SELECT ...
  UNION ALL
  -- 2. Recursive step: references the CTE itself
  SELECT ...
  FROM name JOIN ...
)
SELECT * FROM name;

Percorrere un organigramma

Trovi tutti i dipendenti che riferiscono, direttamente o indirettamente, a un determinato responsabile:

WITH RECURSIVE reports AS (
  -- anchor: the manager themself
  SELECT id, full_name, manager_id, 0 AS depth
  FROM employees WHERE id = 42

  UNION ALL

  -- recurse: people whose manager is in reports
  SELECT e.id, e.full_name, e.manager_id, r.depth + 1
  FROM employees e
  JOIN reports r ON r.id = e.manager_id
)
SELECT * FROM reports ORDER BY depth, full_name;

Commenti annidati

Percorri un albero di discussione a partire da una radice:

WITH RECURSIVE thread AS (
  SELECT id, parent_id, body, 0 AS depth, ARRAY[id] AS path
  FROM comments WHERE id = $1
  UNION ALL
  SELECT c.id, c.parent_id, c.body, t.depth + 1, t.path || c.id
  FROM comments c
  JOIN thread t ON c.parent_id = t.id
)
SELECT * FROM thread ORDER BY path;

Terminazione

La ricorsione si arresta quando il passaggio ricorsivo non restituisce nuove righe.

Evitare i cicli infiniti

Se il grafo contiene cicli, tenga traccia dei nodi visitati:

WITH RECURSIVE walk AS (
  SELECT id, ARRAY[id] AS path FROM nodes WHERE id = $1
  UNION ALL
  SELECT e.target_id, w.path || e.target_id
  FROM edges e
  JOIN walk w ON e.source_id = w.id
  WHERE e.target_id <> ALL(w.path)
)
SELECT * FROM walk;

Serie numeriche

Le CTE ricorsive possono anche generare sequenze:

WITH RECURSIVE n(i) AS (
  VALUES (1)
  UNION ALL
  SELECT i + 1 FROM n WHERE i < 100
)
SELECT i, i*i AS square FROM n;

Distinta base

Scomponi un prodotto in tutti i suoi componenti, inclusi i sottoassiemi:

WITH RECURSIVE bom AS (
  SELECT part_id, sub_part_id, qty FROM parts WHERE part_id = $1
  UNION ALL
  SELECT p.part_id, p.sub_part_id, p.qty * bom.qty
  FROM parts p
  JOIN bom ON bom.sub_part_id = p.part_id
)
SELECT sub_part_id, SUM(qty) AS total_qty FROM bom GROUP BY sub_part_id;

Limiti di profondità

Per sicurezza, limiti la profondità della ricorsione:

WITH RECURSIVE tree AS (
  SELECT id, parent_id, 0 AS depth FROM nodes WHERE id = $1
  UNION ALL
  SELECT n.id, n.parent_id, t.depth + 1
  FROM nodes n JOIN tree t ON n.parent_id = t.id
  WHERE t.depth < 10
)
SELECT * FROM tree;

UNION e UNION ALL

UNION ALL è la scelta abituale. UNION elimina i duplicati: è utile quando un nodo può essere raggiunto in più modi.

Prestazioni

Le CTE ricorsive vengono valutate iterativamente. La «tabella di lavoro» di ogni passaggio è costituita dalle righe prodotte dal passaggio precedente. Indicizzi le colonne utilizzate per i join.

Riepilogo

Le CTE ricorsive attraversano gerarchie e grafi.

Ancora + UNION ALL + passaggio ricorsivo
Si arrestano quando il passaggio ricorsivo non restituisce righe
Utilizzi un array del percorso per interrompere i cicli

Verifica rapida

Quale parola chiave trasforma una CTE in una CTE ricorsiva?

Rispondi nella lezione interattiva

Gratis per iniziare

Impara SQL con un tutor IA — gratis

Scrivi ed esegui vero codice nel tuo browser, ricevi aiuto istantaneo da un tutor IA disponibile 24/7, e riprendi da dove hai lasciato sul web o nell'app.

Corsi: 46
Lezioni: 183

Inizia a imparare gratis Visualizza il corso completo

Domande Frequenti

La lezione «CTE ricorsive per le gerarchie» è gratuita?

Sì — il testo completo di «CTE ricorsive per le gerarchie» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.

Cosa imparerò in «CTE ricorsive per le gerarchie»?

Attraversi dati gerarchici, come organigrammi, commenti concatenati e grafi, con WITH RECURSIVE e condizioni di arresto Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare SQL Academy?

Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 4 di 4.

Quanto tempo richiede la lezione «CTE ricorsive per le gerarchie»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione SQL Academy?

Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

Sottoquery scalari, di riga e di tabella
Sottoquery correlate e non correlate
Espressioni di tabella comuni (WITH)
CTE ricorsive per le gerarchie

← Torna a SQL Academy