Aflați interogarea recursivă PostgreSQL prin exemplul

rezumat: în acest tutorial, veți afla despre interogarea recursivă PostgreSQL folosind expresii de tabel comune recursive sau CTEs.

Introducere în interogarea recursivă PostgreSQL

PostgreSQL oferă instrucțiunea WITH care vă permite să construiți instrucțiuni auxiliare pentru utilizare într-o interogare.

aceste afirmații sunt adesea denumite expresii comune de tabel sau CTEs. CTEs sunt ca tabele temporare care există numai în timpul executării interogării.

o interogare recursivă este o interogare care se referă la un CTE recursiv. Interogările recursive sunt utile în multe situații, cum ar fi interogarea datelor ierarhice, cum ar fi structura organizațională, factura de materiale etc.

următoarele ilustrează sintaxa unui CTE recursiv:

Code language: SQL (Structured Query Language) (sql)

un CTE recursiv are trei elemente:

• termen Non-recursiv: termenul non-recursiv este o definiție de interogare CTE care formează setul de rezultate de bază al structurii CTE.
• termen recursiv: termenul recursiv este una sau mai multe definiții de interogare CTE Unite cu termenul nerecursiv folosind uniunea sau UNION ALL operator. Termenul recursiv face referire la numele CTE în sine.
• verificare terminare: recursivitatea se oprește atunci când nu sunt returnate rânduri din iterația anterioară.

PostgreSQL execută un CTE recursiv în următoarea secvență:

1. executați termenul nerecursiv pentru a crea setul de rezultate de bază (R0).
2. executați termenul recursiv cu RI ca intrare pentru a returna setul de rezultate Ri+1 ca ieșire.
3. repetați pasul 2 până când se returnează un set gol. (verificare terminare)
4. returnați setul de rezultate final care este o uniune sau UNION ALL a setului de rezultate R0, R1, … Rn

exemplu de interogare recursivă PostgreSQL

vom crea un nou tabel pentru a demonstra interogarea recursivă PostgreSQL.

Code language: SQL (Structured Query Language) (sql)

tabelul employees are trei coloane: employee_id, manager_idși full_name. Coloana manager_id specifică ID-ul managerului unui angajat.

următoarea instrucțiune introduce date eșantion în tabelul employees.

Code language: PHP (php)

următoarea interogare returnează toți subordonații managerului cu id-ul 2.

Code language: SQL (Structured Query Language) (sql)

cum funcționează:

• CTE recursiv, subordonații, definește un termen nerecursiv și un termen recursiv.
• termenul nerecursiv returnează setul de rezultate de bază R0 care este angajatul cu id-ul 2.

Code language: SQL (Structured Query Language) (sql)

termenul recursiv returnează subordonații direcți ai ID-ului angajatului 2. Acesta este rezultatul aderării între tabelul angajaților și subordonații CTE. Prima iterație a termenului recursiv returnează următorul set de rezultate:

Code language: SQL (Structured Query Language) (sql)

PostgreSQL execută termenul recursiv în mod repetat. A doua iterație a membrului recursiv folosește setul de rezultate de mai sus ca valoare de intrare și returnează acest set de rezultate:

Code language: SQL (Structured Query Language) (sql)

a treia iterație returnează un set de rezultate goale, deoarece nu există niciun angajat care să raporteze angajatului cu id-urile 16, 17, 18, 19 și 20.

PostgreSQL returnează setul de rezultate finale care este unirea tuturor seturilor de rezultate în prima și a doua iterații generate de termenii nerecursivi și recursivi.

în acest tutorial, ați învățat cum să utilizați CTEs recursive pentru a construi interogări recursive PostgreSQL.