SQL – Rekursive Abfrage in verschachtelten Datensätzen

Wer viel mit SQL-Datenbanken arbeitet kennt die Möglichkeit, in SQL-Datenbank eine „Sicht“ erstellen zu können. Mit Sichten kann man SQL-Anfragen definieren, um z.B. Daten in Excel zu importieren, um anschließend auswerten zu können. Eine SQL-Abfrage kann im Entwurf-Modus per Drag and Drop schnell erstellt werden. Möchte man die Abfrage ausweiten, in dem man z.B. auch rekursive Abfragen verwenden möchte, so kommt man an der Erstellung einer Funktion nicht vorbei.



Als Beispiel möchte ich eine rekursive Abfrage in meiner Inventory-Datenbank zeigen. Die Datenbank hat 3 Tabellen: „device„, „location“ und die Zuordnungstabelle „device2location„. In der Tabelle „device“ werden alle Geräte erfasst. Die Tabelle „location“ enthält alle Locations bzw. Orte. Die Locations können verschachtelt werden, z.B. ein Hauptgebäude enthält mehrere Stockwerke, die wiederum viele Räume haben können:

Hauptgebäude A
Erdgeschoss
Raum 1
Raum 2
...
1 Obergeschoss
Raum 11
Raum 12
... 
2 Obergeschoss 
Raum 21
Raum 22
... 
Hauptgebäude B
Erdgeschoss 
Raum 1
Raum 2
... 
1 Obergeschoss 
Raum 11
Raum 12
...

Das Hauptgebäude hat bei mir immer die partentID „0„.

In der Zuordnungstabelle „device2location“ kann ich einer Location eins oder mehrere Geräte zuweisen.

Der Aufbau der Datenbank sieht so aus:

Die Abfrage soll alle Geräte auflisten, die noch im Einsatz sind (status = 1), mit der Angabe der Location „Hauptgebäude X -> Stockwerk -> Raum„.

Die Sicht enthält also 3 Tabellen. Die Query wurde von mir manuell angepasst:

SELECT dbo.device.hersteller, dbo.device.modell, dbo.device.sn_nummer AS [ServiceTag / Seriennummer], getLocationPath_1.Path AS Location
FROM dbo.device INNER JOIN dbo.device2location ON dbo.device.ID = dbo.device2location.deviceID INNER JOIN dbo.location ON dbo.device2location.locationID = dbo.location.ID INNER JOIN dbo.getLocationPath() AS getLocationPath_1 ON dbo.location.ID = getLocationPath_1.ID
WHERE (dbo.device.status = 1)
GROUP BY dbo.device.hersteller, dbo.device.modell, dbo.device.sn_nummer, getLocationPath_1.parentID, getLocationPath_1.Path
HAVING (getLocationPath_1.parentID = 0)

Die Query bzw. Abfrage verwendet eine Funktion „getLocationPath„, um die Locations rekursiv aus der Tabelle „location“ auszulesen. Die Funktion liest also so lange aus, bis die Verschachtelung eines Hauptgebäudes durch ist. Dabei beginnt die Auslesung mit der parentID = 0. Mit „GROUP BY“ werden alle Werte gruppiert, da wir sonst pro Gerät mehrere Werte ausgegeben bekommen hätten.

Hier ist die besagte Funktion:

USE [Inventory]
GO
/****** Object:  UserDefinedFunction [dbo].[getLocationPath]    Script Date: 01/11/2019 09:57:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Wolfgang, Einhorn>
-- Create date: <11.01.2019>
-- Description:	<Diese Funktion generiert Location-Pfade>
-- =============================================
CREATE [ OR ALTER ] FUNCTION [dbo].[getLocationPath]()
RETURNS table
AS
return
(
	WITH Locations(ID, parentID, Path, Level) 
	AS 
	(
			SELECT     ID, parentID, CAST(Title AS VARCHAR(MAX)), CONVERT(int, 0)
			FROM       dbo.location AS FirtGeneration
			WHERE      FirtGeneration.Active = 1
			UNION ALL
			SELECT     Parent.ID, NextGeneration.parentID,
					   CAST(CASE WHEN Parent.Path = '' 
					   THEN (CAST(NextGeneration.Title AS VARCHAR(MAX))) 
					   ELSE (CAST(NextGeneration.Title AS VARCHAR(MAX)) + '->' + Parent.Path) 
					   END AS VARCHAR(MAX)), 
					   Level - 1
			FROM       dbo.location AS NextGeneration INNER JOIN
					   Locations AS Parent ON NextGeneration.ID = Parent.parentID
			WHERE      NextGeneration.Active = 1
	)
	select ID, parentID, Path, Level from Locations
)

Schreibe einen Kommentar

Diese Website verwendet Akismet, um Spam zu reduzieren. Erfahre mehr darüber, wie deine Kommentardaten verarbeitet werden.