Leer PostgreSQL recursieve Query door voorbeeld

samenvatting: in deze tutorial leert u over de PostgreSQL recursieve query met behulp van recursieve algemene tabeluitdrukkingen of CTE ‘ s.

Inleiding tot de PostgreSQL recursieve query

PostgreSQL biedt het WITH statement waarmee u hulpcommando ‘ s kunt construeren voor gebruik in een query.

deze statements worden vaak aangeduid als common table expressions of CTE ‘ s. De CTE ‘ s zijn als tijdelijke tabellen die alleen bestaan tijdens het uitvoeren van de query.

een recursieve query is een query die verwijst naar een recursieve CTE. De recursieve query ‘ s zijn nuttig in veel situaties, zoals het opvragen van hiërarchische gegevens zoals organisatiestructuur, bill of materials, enz.

het volgende illustreert de syntaxis van een recursieve CTE:

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

een recursieve CTE heeft drie elementen:

• niet-recursieve term: de niet-recursieve term is een CTE-query-definitie die de basisresultaatset van de CTE-structuur vormt.
• recursieve term: de recursieve term is een of meer CTE-query-definities verbonden met de niet-recursieve term met behulp van de operator UNION of UNION ALL. De recursieve term verwijst naar de CTE naam zelf.
• Terminatiecontrole: de recursie stopt wanneer er geen rijen worden geretourneerd van de vorige iteratie.

PostgreSQL voert een recursieve CTE uit in de volgende reeks:

1. Voer de niet-recursieve term uit om de basisresultaatset (R0) te maken.
2. voer recursieve term uit met Ri als invoer om het resultaat te retourneren set Ri + 1 als uitvoer.
3. Herhaal stap 2 totdat een lege set wordt teruggegeven. (termination check)
4. Geef de eindresultaatset terug die een UNION of UNION ALL is van de resultaatset R0, R1, … Rn

PostgreSQL recursieve query voorbeeld

we zullen een nieuwe tabel maken om de PostgreSQL recursieve query aan te tonen.

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

de tabel employees bevat drie kolommen: employee_id, manager_id en full_name. De kolom manager_id specificeert de manager-id van een werknemer.

de volgende verklaring voegt steekproefgegevens in de employees tabel.

Code language: PHP (php)

de volgende query geeft alle ondergeschikten van de manager met de id 2.

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

Hoe werkt het:

• de recursieve CTE, subordinates, definieert een niet-recursieve term en een recursieve term.
• de niet-recursieve term geeft de basisresultaatset R0 terug die de werknemer met id 2 is.

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

de recursieve term geeft de directe ondergeschikte(en) van de werknemer-id 2 terug. Dit is het resultaat van de samenvoeging tussen de werknemers tabel en de ondergeschikten CTE. De eerste iteratie van de recursieve term geeft de volgende resultaatset terug:

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

PostgreSQL voert de recursieve term herhaaldelijk uit. De tweede iteratie van het recursieve lid gebruikt de resultaatset bovenstaande stap als de invoerwaarde, en retourneert deze resultaatset:

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

de derde iteratie geeft een lege result set omdat er geen werknemer rapportage aan de werknemer met de id 16, 17, 18, 19, en 20.

PostgreSQL geeft de uiteindelijke resultaatverzameling terug die de vereniging is van alle resultaatverzamelingen in de eerste en tweede iteraties gegenereerd door de niet-recursieve en recursieve termen.

in deze tutorial hebt u geleerd hoe u de recursieve CTE ‘ s kunt gebruiken om de PostgreSQL recursieve queries te construeren.