Wie man alle Mitarbeiter unter jedem Manager in SQL findet

Haben Sie schon einmal die Aufgabe gehabt, ein Organigramm zu erstellen - d.h. die Mitarbeiter unter jedem Manager in einem Unternehmen zu finden? Mit SQL ist das einfacher, als Sie denken! Dieser Artikel zeigt Ihnen, wie Sie die Antwort mithilfe einer rekursiven Abfrage erhalten.

In der Geschäftswelt ist es eine sehr häufige Aufgabe, alle unterstellten Mitarbeiter eines bestimmten Managers aufzulisten. Ist diese Aufgabe so schwierig, dass sie einen ganzen Artikel rechtfertigt? Wenn man es sich wie ein Organigramm vorstellt, scheint es ziemlich einfach zu sein: Man sucht den Vorgesetzten, listet alle ihm unterstellten Mitarbeiter auf, und das war's. Wozu die Aufregung?

Die Schwierigkeit besteht darin, dass SQL mit Tabellen arbeitet, nicht mit Organigrammen. Wenn ein Organigramm in eine Tabelle übersetzt wird, kann es etwas schwierig werden, das Gewünschte zu erreichen.

Die Technik, die ich in diesem Artikel verwenden werde, basiert hauptsächlich auf SQL-Joins und rekursiven Abfragen. Wenn Sie etwas Übung brauchen (oder umfassendere Erklärungen), sehen Sie sich unsere SQL-JOINs und Rekursive Abfragen Kurse an.

Sehen wir uns zunächst an, wie ein Organigramm normalerweise in einer für SQL-Abfragen geeigneten Tabelle dargestellt wird.

Mitarbeiter-Tabelle

Wir verwenden eine Tabelle namens Mitarbeiter die die folgenden Spalten enthält:

• Mitarbeiter_ID: Die ID des Mitarbeiters.
• Vorname: Der Vorname des Mitarbeiters.
• Nachname: Der Nachname des Mitarbeiters.
• Manager_ID: Die ID des Vorgesetzten des Mitarbeiters.

In diesem Beispiel gibt es zehn Angestellte. Schauen wir uns die Datensätze an:

Hier gibt es nichts Kompliziertes, nur eine Liste von Angestellten. Das Hauptmerkmal ist die Spalte Manager_ID, die die ID des Vorgesetzten eines jeden Mitarbeiters enthält. Ein Beispiel:

Nancy Young (Mitarbeiter_ID 4529) hat einen Chef. Die ID ihres Chefs ist 4125 und steht in der Spalte Manager_ID. Wenn Sie nach diesem Wert in der Spalte Manager_ID suchen, finden Sie heraus, dass dies die ID von Mafalda Ranieri ist.

Werfen wir nun einen Blick auf Frau Mafalda Ranieri:

In der Spalte Manager_ID steht ein NULL-Wert, was bedeutet, dass Mafalda Ranieri keine übergeordnete Führungskraft hat, d.h. sie ist die Präsidentin des Unternehmens.

Die Logik besagt, dass immer dann, wenn ein NOT NULL-Wert in der Spalte Manager_ID vorhanden ist, dieser Mitarbeiter einem Manager unterstellt ist. Natürlich ist es möglich, dass ein Angestellter ein Manager ist und auch jemanden über sich in der Hierarchie hat.

Alle Mitarbeiter unter jedem Manager zählen

Lassen Sie uns diese "Aufwärmübung" machen. Verwenden Sie nur die Tabelle Mitarbeiter. Wie würden Sie alle Mitarbeiter unter jedem Manager zählen? So könnten Sie es machen, wobei die Selbstverknüpfung der Tabelle der magische Trick ist:

SELECT	
sup.Mitarbeiter_ID,
	sup.Vorname,
	sup.Nachname,
	COUNT (sub.Mitarbeiter_ID) AS Anzahl_Mitarbeiter
FROM Mitarbeiter sub 
JOIN Mitarbeiter sup 
ON sub.Manager_ID = sup.Mitarbeiter_ID
GROUP BY sup.Mitarbeiter_ID, sup.Vorname, sup.Nachname;

Wenn Sie mit Self-Joins nicht allzu vertraut sind, lesen Sie diesen Artikel, der Self-Joins anhand von Beispielen erklärt.

Beim Self-Joining der Tabelle Mitarbeiter habe ich zwei Aliasnamen erstellt, damit es einfacher ist, zu verstehen, was ich tue. Die eine Tabelle hat den Alias sub (für untergeordnete Mitarbeiter); der Alias der anderen Tabelle lautet sup (für vorgesetzte Mitarbeiter). Da ich nach analytischen Daten von Managern suche, habe ich die Abfrage angewiesen, die Spalten Mitarbeiter_ID, Vorname und Nachname aus der Tabelle sup zurückzugeben.

Der Code zählt dann die Anzahl der Mitarbeiter mit der Funktion COUNT() in der Spalte sub.Mitarbeiter_ID. Sie würden das gleiche Ergebnis erhalten, wenn Sie stattdessen sup.Mitarbeiter_ID verwenden würden. Ich wollte nur die Tabelle sub Tabelle verwenden, um diesen Schritt auf folgende Weise zu erklären: "Aha, die Tabelle sub ist für unterstellte Mitarbeiter, also zähle ich logischerweise die Anzahl der unterstellten Mitarbeiter in dieser Tabelle".

Wie ich bereits sagte, verknüpfe ich die Mitarbeiter Tabelle mit zwei Aliasen. Die Verknüpfungsbedingung lautet sub.Manager_ID = sup.Mitarbeiter_ID. Das erscheint logisch, denn der Wert in der Spalte Manager_ID ist die ID des Managers als Mitarbeiter und steht natürlich auch in der Spalte Mitarbeiter_ID.

Ich habe die Aggregatfunktion COUNT() verwendet, also muss ich das Ergebnis nach den Spalten Mitarbeiter_ID, Vorname und Nachname gruppieren.

Wenn Sie den Code ausführen, wird er dieses Ergebnis zurückgeben:

Die Tabelle zeigt vier Manager und die Anzahl ihrer Untergebenen (Anzahl_Mitarbeiter).

Alle direkten Untergebenen jedes Managers finden

Die Suche nach direkt unterstellten Mitarbeitern ähnelt dem obigen Problem. Es ist logisch zu denken, dass ich, wenn ich die Anzahl der unterstellten Mitarbeiter gefunden habe, auch deren Namen finden könnte. Um die Wahrheit zu sagen, ist die Lösung für diese Übung nur eine Variation des vorherigen Codes:

SELECT	
sub.Mitarbeiter_ID AS Untergebener_ID,
	sub.Vorname AS Untergebener_Vorname,
	sub.Nachname AS Untergebener_Nachname,
	sup.Mitarbeiter_ID AS Vorgesetzer_ID,
	sup.Vorname AS Vorgesetzter_Vorname,
	sup.Nachname AS Vorgesetzter_Nachname
FROM Mitarbeiter sub 
JOIN Mitarbeiter sup 
ON sub.Manager_id = sup.Mitarbeiter_ID
ORDER BY Vorgesetzer_ID;

Das Prinzip ist hier das gleiche; die Tabelle Mitarbeiter ist selbst-verknüpft, wobei die Spalte sub.Manager_ID = sup.Mitarbeiter_ID ist. Dazu verwende ich wieder die Aliasnamen sub und sup.

Zunächst nehme ich die Spalten sub.Mitarbeiter_ID, sub.Vorname und sub.Nachname. Ich benenne sie so um, dass sie die Tatsache widerspiegeln, dass sich die Daten auf die unterstellten Mitarbeiter beziehen. Das Gleiche mache ich mit diesen Spalten für die Tabelle sup, aber dieses Mal spiegelt der Name die Position des Mitarbeiters als Vorgesetzter wider.

Schließlich wird das Ergebnis nach der Spalte Vorgesetzer_ID sortiert:

Es scheint, dass die Tabelle das zeigt, was ich beabsichtigt habe: Wir haben die Namen der Mitarbeiter und die Namen ihrer Vorgesetzten. Meinem Adlerauge als Ex-Auditor fällt jedoch auf, dass es nur neun Mitarbeiter gibt, obwohl ich weiß, dass es zehn Mitarbeiter im Unternehmen gibt. Warum ist das so? Der Grund ist Mafalda Ranieri. Sie ist die Präsidentin des Unternehmens, hat also keinen Vorgesetzten und ist niemandem unterstellt. Dies wird als Wert NULL in der Spalte Manager_ID dargestellt:

Sie fehlt im Abfrageergebnis aufgrund der Art des Self-Join. Ich habe JOIN (d.h. INNER JOIN) verwendet. Diese Art der Verknüpfung gibt keine Zeilen mit NULL Werten zurück. Wenn Sie auch diese Zeilen in Ihrem Abfrageergebnis sehen möchten, müssen Sie LEFT JOIN anstelle von JOIN verwenden. Alles andere in der Abfrage bleibt gleich. Dieser Teil des Codes wird wie folgt aussehen:

...
FROM Mitarbeiter sub LEFT JOIN Mitarbeiter sup ON sub.Manager_ID = sup.Mitarbeiter_ID
...

Wenn Sie den geänderten Code ausführen, erhalten Sie alle Mitarbeiter, auch diejenigen, die keinen Vorgesetzten haben:

Dieses Ergebnis kann jedoch immer noch unbefriedigend sein, da es nur direkte Untergebene zurückgibt. In komplexeren Organisationen haben einige Manager direkte Untergebene, die andere Mitarbeiter führen. Diese Mitarbeiter haben einen direkten Vorgesetzten, aber sie sind auch dem Vorgesetzten ihres Vorgesetzten unterstellt. Ist es überhaupt möglich, einen Manager auszuwählen und die Liste aller seiner direkten und indirekten Untergebenen in SQL zu erhalten? Aber sicher doch - mit ein wenig Hilfe unserer Freundin, der rekursiven Abfrage.

Wenn Sie mit dem Konzept der rekursiven Abfragen nicht sehr vertraut sind, sollten Sie diesen Artikel lesen, in dem erklärt wird , wie rekursive Abfragen funktionieren, bevor Sie fortfahren.

Sowohl direkte als auch indirekte Untergebene unter jedem Manager finden

In der Tabelle Mitarbeiter haben wir eine Mitarbeiterin namens Nancy Young. Sie hat nur eine Vorgesetzte, die Präsidentin des Unternehmens. Nancy ist eine Top-Führungskraft; natürlich hat sie Untergebene. Auch ihre Untergebenen haben ihre eigenen Untergebenen. Ich möchte Ihnen nun zeigen, wie Sie alle direkten und indirekten Untergebenen einer Führungskraft - in diesem Fall Nancy Young - ermitteln können. Ich werde dazu eine rekursive CTE ("Common Table Expression") verwenden.

Der Code, der mir das Gewünschte liefert, lautet wie folgt:

WITH RECURSIVE Untergebener AS (
	SELECT	Mitarbeiter_ID,
			Vorname,
			Nachname,
			Manager_ID,
			0 AS Ebene
	FROM Mitarbeiter
	WHERE Mitarbeiter_ID = 4529

	UNION ALL

	SELECT	e.Mitarbeiter_ID, 
			e.Vorname,
			e.Nachname,
			e.Manager_ID,
			Ebene + 1
	FROM Mitarbeiter e 
JOIN Untergebener s 
ON e.Manager_ID = s.Mitarbeiter_ID
)

SELECT	
s.Mitarbeiter_ID,
	s.Vorname AS Untergebener_Vorname,
	s.Nachname AS Untergebener_Nachname,
	m.Mitarbeiter_ID AS Direkter_Vorgesetzter_ID,
	m.Vorname AS Direkter_Vorgesetzter_Vorname,
	m.Nachname AS Direkter_Vorgesetzter_Nachname,
	s.Ebene
FROM Untergebener s 
JOIN Mitarbeiter m 
ON s.Manager_ID = m.Mitarbeiter_ID
ORDER BY Ebene;

In der Standard-SQL-Syntax beginnen Sie, wenn Sie eine rekursive CTE haben wollen, mit dem Schreiben des Codes, indem Sie WITH RECURSIVE schreiben. Wichtiger Hinweis! Wenn Sie SQL Server verwenden, wird dies nicht funktionieren. Sie müssen die obige Abfrage ohne RECURSIVE schreiben. Mit anderen Worten, die erste Zeile des Codes muss wie folgt aussehen:

WITH Untergebener AS (
...

Lassen Sie uns noch einmal erklären, was der obige Code bewirkt. Es wird eine rekursive CTE mit dem Namen Untergebener erstellt. Die erste SELECT Anweisung in dieser CTE gibt die Spalten aus der Tabelle Mitarbeiter zurück. Ich habe auch eine neue Spalte hinzugefügt: Ebene. Nancy Young wird die Führungskraft der Ebene 0 sein; den Zweck dieser Spalte werden Sie später noch sehen. Da die Mitarbeiter-ID von Nancy Young 4529 ist, habe ich diese ID in der WHERE -Klausel hinzugefügt.

Ich möchte, dass das Ergebnis der Anweisung SELECT mit dem Ergebnis der zweiten Anweisung SELECT "zusammengeführt" wird. Dazu müssen beide SELECT Anweisungen die gleiche Anzahl von Spalten im Ergebnis haben. Damit UNION ALL Sinn macht, füge ich die Spalten Mitarbeiter_ID, Vorname, Nachname und Manager_ID in die zweite SELECT-Anweisung ein.

Die letzte Spalte in der Anweisung ist der Wert von Ebene aus der ersten Anweisung SELECT (0). Diesem Wert wird bei jeder Rekursion eine 1 hinzugefügt, wodurch die Hierarchieebenen zurückgegeben werden. Dies ist nützlich, um Daten zu sortieren und um leicht zu verfolgen, wer wessen Manager ist. Ich habe die Tabelle Mitarbeiter mit der CTE selbst verbunden. Ich behandle die CTE wie eine Tabelle (was sie auch ist), gebe ihr einen Alias und verbinde beide Tabellen auf e.Manager_ID = s.Mitarbeiter_ID.

Schließlich komme ich zu der Anweisung SELECT außerhalb der CTE. In diesem Teil des Codes habe ich die CTE selbst mit der Tabelle Mitarbeiter verbunden. Zunächst werden die Spalten Mitarbeiter_ID, Vorname und Nachname aus der CTE ausgewählt, da ich die CTE als Quelle für die untergeordneten Daten verwende. Ich habe diese Spalten auch entsprechend umbenannt, um Verwechslungen zu vermeiden.

Der nächste Schritt besteht darin, die gleichen Spalten aus der Tabelle Mitarbeiter auszuwählen. Diese Spalten enthalten die Daten der direkten Vorgesetzten der Mitarbeiter.

Schließlich wird das Ergebnis nach der Spalte Ebene geordnet, wodurch die Mitarbeiter hierarchisch sortiert werden. Eine so lange Abfrage kann nur ein schönes Ergebnis liefern. Sehen Sie selbst:

Die Tabelle zeigt uns alle direkten und indirekten Untergebenen von Nancy Young zusammen mit ihren direkten Vorgesetzten. Schauen Sie sich die Tabelle an und Sie werden sehen, dass Nancy Young zwei direkte Untergebene hat: Jakub Hrabal und Sandra Rajkovic. Jakub hat keine Untergebenen, aber Sandra hat zwei von ihnen: Moira Areas und Jon Nilssen.

Sehen Sie? Es ist gar nicht so einfach, an etwas heranzukommen, von dem man denkt, dass es einfach ist. Glücklicherweise sind rekursive Abfragen in solchen Fällen sehr nützlich.