Wie man die Nachkommen eines Elternteils in SQL abruft

Möchten Sie lernen, wie man mit Stammbäumen umgeht und die Nachkommen eines Elternteils findet? In diesem Artikel erfahren Sie, wie Sie mit hierarchischen Daten umgehen können.

Die Suche nach Nachkommen eines Elternteils ist ein häufiges Problem in SQL. Wenn Sie sich einen Stammbaum vorstellen, ist die Eltern-Kind-Beziehung der Grundbaustein, der die Beziehungen innerhalb des Stammbaums bildet. Die Eltern-Kind-Beziehung ist genau das, was alle hierarchischen Daten ausmacht.

Ein weiteres Beispiel für hierarchische Daten ist die Manager-Mitarbeiter-Beziehung. Hierarchische Daten finden sich in zahlreichen anderen Situationen, z. B. in detaillierten Daten über Projekte, ihre Unterprojekte und jede Aufgabe, die in Unteraufgaben unterteilt werden kann. Ein weiteres Beispiel für hierarchische Daten ist die Erstellung einer Klassifikation von Lebewesen, bei der Sie die Lebewesen nach Reich, Stamm, Klassen, Ordnung, Familien, Gattung und Art einteilen. Oder Sie betrachten ein Musikgenre und seine Untergattungen. Die Bluesmusik kann beispielsweise in Untergattungen wie Delta-Blues, Country-Blues, Jump-Blues und Chicago-Blues unterteilt werden. Sie beeinflusste einen neuen Stil, die Soulmusik. Die Soulmusik hat ihre Untergattungen wie Southern Soul, Motown Soul, Philly Soul, Progressive Soul usw., die wiederum die Entstehung anderer Genres wie Funk oder Disco beeinflusst haben.

In diesem Artikel werden die Themen behandelt, die Sie im Kurs Rekursive Abfragen näher kennen lernen können. Es gibt einen Leitfaden, in dem beschrieben wird, wie dieses Wissen für Sie von Nutzen sein kann; die Lektüre kann Ihnen bei der Entscheidung helfen, ob Sie rekursive Abfragen lernen möchten.

Aufbau einer hierarchischen Tabelle

Sie werden ein bestimmtes Merkmal von Tabellen bemerken, die Daten mit Eltern-Kind-Beziehungen enthalten: zwei Spalten mit ID-Werten. Die eine Spalte ist die ID, die zur eindeutigen Identifizierung der Daten in der Tabelle verwendet wird. Die andere Spalte enthält die ID, die zur Referenzierung einer anderen Zeile verwendet wird.

Lassen Sie mich Ihnen zeigen, was ich damit meine. Die Tabelle, die Sie verwenden werden, heißt family_treemit den folgenden Spalten:

• id: Die ID der Person und der Primärschlüssel (PK) für die Tabelle.
• first_name: Der Vorname der Person.
• last_name: Nachname der Person.
• parent_id: Die Eltern-ID der Person.

Hier sind einige Daten aus der Tabelle:

Sie sehen, dass die ID von John McArthur 2 ist. Die ID von Sam McArthur ist 5, während seine parent_id = 2. Das bedeutet, dass sein Elternteil die ID 2 hat; in diesem Fall ist es John McArthur.

Diese Datenstruktur ist charakteristisch für hierarchische Daten, die in Datenbanken recht häufig vorkommen. Um Daten aus einer solchen Tabelle zu erhalten, müssen Sie hierarchische oder rekursive Abfragen verwenden. Wenn Sie wissen wollen, was rekursive Abfragen leisten können, lesen Sie den Artikel, der ihre Leistungsfähigkeit aufzeigt. Ich werde konkreter und zeige Ihnen, wie Sie eine rekursive Abfrage strukturieren, um alle Nachkommen eines Elternteils aus der obigen Tabelle zu erhalten.

Rekursive Abfrage: Abrufen der Nachkommenschaft

Zunächst möchte ich Ihnen zeigen, wie die Abfrage aussieht. Dann werde ich sie für Sie aufschlüsseln, alle Teile der Abfrage analysieren und deren Zweck erklären.

Los geht's! Die Abfrage, mit der Sie alle Nachkommen eines Elternteils erhalten, ist diese:

WITH RECURSIVE descendant AS (
	SELECT	id,
			first_name,
			last_name,
			parent_id,
			0 AS level
	FROM family_tree
	WHERE id = 1

	UNION ALL

	SELECT	ft.id,
			ft.first_name,
			ft.last_name,
			ft.parent_id,
			level + 1
	FROM family_tree ft
JOIN descendant d
ON ft.parent_id = d.id
)

SELECT	d.id AS descendant_id,
		d.first_name AS descendant_first_name,
		d.last_name AS descendant_last_name,
		a.id AS ancestor_id,
		a.first_name AS ancestor_first_name,
		a.last_name AS ancestor_last_name,
		d.level
FROM descendant d
JOIN family_tree a
ON d.parent_id = a.id
ORDER BY level, ancestor_id;

Die Abfrage beginnt mit der Definition des CTE. Die drei Buchstaben stehen für Common Table Expression ( Gemeinsamer Tabellenausdruck), und Sie erkennen eine CTE, wenn Sie das Wort WITH sehen. Mehr Theorie hinter dem CTE und seine Syntax werden in einem anderen Artikel erklärt.

CTEs können rekursiv oder nicht rekursiv sein. Sie fragen sich, wie man zwischen diesen beiden unterscheiden kann? Es ist einfacher, als Sie denken: Die CTEs, die mit WITH RECURSIVE beginnen, sind rekursiv.

Wenn Sie eine CTE schreiben, müssen Sie ihren Namen angeben. In meinem Fall wird die CTE durch die folgende Codezeile descendant genannt: WITH RECURSIVE descendant AS. Was danach folgt, ist eine SELECT -Anweisung in Klammern, die als CTE gespeichert wird. In dieser SELECT Anweisung wähle ich alle Spalten aus der Tabelle family_tree. Außerdem füge ich eine neue Spalte namens level hinzu, die den Wert 0 enthalten wird. Haben Sie etwas Geduld; Sie werden den Zweck dieses Schrittes gleich sehen. Schließlich verwende ich eine WHERE Klausel, um die Ergebnisse zu filtern. Ich bin daran interessiert, alle Nachkommen eines Peter McArthur zu finden, dessen id = 1.

Dann "verschmelze" ich diese SELECT Anweisung mit der nächsten, indem ich UNION ALL verwende. Mit diesem Befehl können Sie die Ergebnisse von zwei oder mehr SELECT -Anweisungen kombinieren. Damit UNION ALL funktioniert, müssen alle SELECT Anweisungen die gleiche Anzahl von Spalten haben. Die folgende SELECT Anweisung selektiert wiederum alle Spalten der Tabelle family_tree. Zusätzlich nimmt sie den Wert der Spalte level (aus der vorherigen SELECT -Anweisung, 0) und fügt bei jeder Rekursion 1 hinzu. Die Daten für diese SELECT -Anweisung werden durch Joinen der Tabelle family_tree mit der CTE, die hier wie jede andere Tabelle behandelt wird. Da die CTE-Daten die gleichen sind wie die Daten in der Tabelle family_treesind, mit Ausnahme der neuen Spalte level, verbindet diese JOIN die Tabelle family_tree mit sich selbst in den Spalten parent_id und id.

Wenn Sie die Klammern schließen, sind Sie mit der Definition des CTE fertig. Nun folgt die Anweisung SELECT, die die CTE verwendet und alle Nachkommen für jede übergeordnete Spalte zurückgibt. In dieser SELECT -Anweisung wähle ich zunächst die Spalten id, first_name und last_name aus dem CTE-Nachfolger aus. Die CTE wird wie eine reguläre Tabelle behandelt, wobei d der Alias ist. Ich verknüpfe sie mit der Tabelle family_tree mit dem Alias a. Ich habe diesen Alias gewählt, weil ich die CTE als Tabelle für die Nachfolgedaten behandle und family_tree als die Tabelle mit den Vorgängerdaten. Das ist nur ein einfacher Trick, damit ich beim Schreiben einer Abfrage nicht durcheinander komme. Die aus der Tabelle ausgewählten Spalten family_tree sind wieder id, first_name und last_name. Die letzte ausgewählte Spalte ist level aus der CTE.

Am Ende werden die Daten nach den Spalten level und ancestor_id geordnet. Hier ist das Ergebnis:

Dies sind alle Nachkommen von Peter McArthur, dessen ID 1 ist. Die Personen auf Ebene 1 sind die Kinder von Peter McArthur. Seine Enkelkinder befinden sich auf Ebene 2, wobei ihre Eltern in der Tabelle aufgeführt sind. Ebene 3 zeigt Peter McArthurs Urenkel, deren Eltern ebenfalls namentlich aufgeführt sind.

Wenn Sie wissen, wie die obige Abfrage zu schreiben ist, können Sie sie auf jedes andere Szenario anwenden, in dem es eine hierarchische Datenstruktur gibt.

Zusätzliche Tipps

Schreiben von rekursiven CTEs in Microsoft SQL Server

Wenn Sie versuchen, die obige Abfrage in SQL Server auszuführen, wird ein Fehler angezeigt. Kein Grund zur Panik; SQL Server unterstützt CTEs und rekursive Abfragen. Der einzige Unterschied ist, dass Sie in SQL Server nicht RECURSIVE schreiben müssen, um eine rekursive Abfrage zu erhalten. Lassen Sie es einfach weg, etwa so:

WITH descendant AS...

Alles andere ist gleich; es besteht keine Notwendigkeit, einen anderen Teil der Abfrage zu ändern. Wenn Sie Oracle verwenden, finden Sie hier einen Artikel, der die Verwendung von hierarchischen Abfragen erklärt.

Abrufen der NULL-Werte in der Tabelle

Wenn Sie sich die Tabelle mit den Nachkommen von Peter McArthur genauer ansehen, werden Sie feststellen, dass es keinen Peter McArthur selbst gibt. Das ist kein Fehler, sondern liegt einfach daran, dass er nicht sein eigener Nachkomme ist. Vielleicht möchten Sie ihn aber auch zeigen, so dass Sie den kompletten Stammbaum vom ersten Vorfahren bis zum letzten Nachkommen haben.

Dazu müssen Sie zunächst wissen, warum Peter in der Ergebnistabelle nicht auftaucht. Der Grund ist, dass ich JOIN verwendet habe, um die Tabellen zu verbinden. Da Peter McArthur der Ausgangspunkt des Stammbaums ist, gibt es einen Wert NULL in der Spalte parent_id. Die JOIN gibt keine NULL Werte zurück.

Es gibt nur eine Kleinigkeit, die Sie in der Abfrage ändern müssen, um die NULL Werte zu erhalten. Anstelle von JOIN verwenden Sie einfach LEFT JOIN. Hier ist der Teil des Codes, der sich ändert; alles andere bleibt gleich:

...FROM descendant d LEFT JOIN family_tree a ON d.parent_id = a.id...

Das Ergebnis unterscheidet sich in einer Zeile, nämlich in der ersten:

Haben Sie den Dreh raus, die Nachkommen eines Elternteils zu finden?

In diesem Artikel haben Sie etwas über die hierarchische Datenstruktur und die Eltern-Kind-Beziehung gelernt. Ich habe Ihnen einige Beispiele aus der Praxis gegeben, in denen Sie solche Datenstrukturen finden können. Das sind natürlich nicht die einzigen.

Sie haben die charakteristische Struktur solcher Daten kennen gelernt, um sich auf das Schreiben einer Abfrage vorzubereiten, mit der Sie alle Nachkommen eines Vorfahren ermitteln können. Anhand dieses Beispiels sollten Sie in der Lage sein, das Wissen in ähnlichen Szenarien anzuwenden. Um das Schreiben von rekursiven Abfragen noch besser zu beherrschen und zu erkennen, wann sie zu verwenden sind, ist es sehr empfehlenswert, mit dem Kurs Rekursive Abfragen zu üben. Der Artikel, in dem erklärt wird, wann CTEs zu verwenden sind, kann Ihnen ebenfalls dabei helfen, besser mit CTEs umzugehen.