Laden...

SQL Query Rekursion mit zwei Stufen

Erstellt von RayYago vor 4 Jahren Letzter Beitrag vor 4 Jahren 1.776 Views
R
RayYago Themenstarter:in
19 Beiträge seit 2019
vor 4 Jahren
SQL Query Rekursion mit zwei Stufen

verwendetes Datenbanksystem: MS SQL

Hallo liebe Foren-User,

folgender Sachverhalt den ich einfach nicht gelöst bekomme mit einer sauberen Abfrage.

Zusammenfassung:
Anhand einer Auftragsnummer (Order_nb) möchte ich verknüpfte Vorgänge ermitteln die über Europaletten verknüpft sind und dann damit alle verwendeten Paletten ermitteln. Die Tiefe der Rekursion ist dabei allerdings variable.

Erklärung:
Es ist so, das eine Palette mehrere Vorgänge beinhalten kann. Eine Vorgang kann aber auch auf mehreren Paletten sein. Sind mehr als ein Vorgang auf einer Palette, gehören die Vorgänge zusammen, da Vorgänge aber auch auf mehreren Paletten sein können, entsteht eine Rekursion. Anhand der Vorgänge möchte ich somit alle verwendeten Paletten ermitteln die insgesamt für alle Verknüpften Vorgänge verwendet wurden.

Hier mal eine kleine Illustration: Dateianhang

Hier sieht man noch einmal das ein Vorgang auf mehreren Paletten sein kann. Eine Palette beinhaltet mehrere Vorgänge.

Ziel:
Mein Ziel ist es alle Paletten zu ermitteln die insgesamt verwendet wurden.

Vermutung/Versuch:
Gegeben ist immer eine Vorgangsnummer, anhand dieser kann ich die ersten Paletten finden, danach müsste ich die Paletten durchgehen und gucken was auf diesen Paletten für Vorgänge sind. Und ab da beginnt es wieder von vorne, ich müsste anhand der Vorgänge die Paletten ermitteln und anhand der Paletten die nächsten Vorgänge etc etc etc..

Ich bekomme diese Rekursion einfach nicht hin, auch mit einer CTE nicht, da ich immer diesen Schritt über die Palette habe.

Ich habe folgendes SQL erstellt das statisch ein paar Tiefen ermittelt, aber das ist sehr unschön und auch garnicht flexible, zeigt aber vielleicht besser was ich genau meine.

SELECT y.order_nb, y.pallet_nb 
FROM 
	(select DISTINCT c.order_nb, c.pallet_nb  
	FROM
		(select o.order_nb, o.pallet_nb 
		from
		(SELECT 
			order_nb,
			pallet_nb
		FROM order_pallet
		WHERE pallet_nb = 130290) t
		INNER JOIN order_pallet o on t.order_nb = o.order_nb) q
	INNER JOIN order_pallet c on q.pallet_nb = c.pallet_nb) x
INNER JOIN order_pallet y on x.order_nb = y.order_nb

Hier meine kläglichen Versuche das ganze rekursiv zu bekommen:

WITH cte AS (
SELECT
order_nb,
pallet_nb
FROM order_pallet
WHERE order_nb = 655843

UNION ALL

SELECT h.order_nb, h.pallet_nb
FROM
(SELECT
e.order_nb,
e.pallet_nb
FROM order_pallet e
INNER JOIN cte o ON e.pallet_nb = o.pallet_nb) b
INNER JOIN order_pallet h on b.order_nb = h.order_nb
)
select * from cte

Eine normale Rekursion bekomme ich hin, aber ich muss abwechselnd zwei Dinge prüfen. Quasi:

-> Für einen Vorgang alle Paletten finden
-> Für gefundene Paletten alle Vorgänge finden
-> Für alle gefundenen Vorgänge alle Paletten finden
-> Für alle gefundenen Paletten alle Vorgänge finden
-> Für alle gefundenen Vorgänge alle Paletten finden

Beispiel Daten: Hier einmal eine Tabelle mit Beispielen mit denen ich ein wenig spiele:

CREATE TABLE [dbo].[order_pallet](
[id] [int] IDENTITY(1,1) NOT NULL,
[order_nb] [int] NULL,
[pallet_nb] [int] NULL,
CONSTRAINT [order_pallet_id] PRIMARY KEY CLUSTERED
([id] ASC))

INSERT INTO order_pallet VALUES (655843, 130290)
INSERT INTO order_pallet VALUES (655844, 130290)
INSERT INTO order_pallet VALUES (655845, 130290)
INSERT INTO order_pallet VALUES (655846, 130290)
INSERT INTO order_pallet VALUES (655847, 130290)

INSERT INTO order_pallet VALUES (655843, 130291)
INSERT INTO order_pallet VALUES (655901, 130291)
INSERT INTO order_pallet VALUES (655902, 130291)
INSERT INTO order_pallet VALUES (655903, 130291)
INSERT INTO order_pallet VALUES (655904, 130291)

INSERT INTO order_pallet VALUES (655101, 130292)
INSERT INTO order_pallet VALUES (655102, 130292)
INSERT INTO order_pallet VALUES (655902, 130292)
INSERT INTO order_pallet VALUES (655103, 130292)
INSERT INTO order_pallet VALUES (655904, 130292)

INSERT INTO order_pallet VALUES (655545, 130293)
INSERT INTO order_pallet VALUES (655535, 130293)
INSERT INTO order_pallet VALUES (655525, 130293)
INSERT INTO order_pallet VALUES (655103, 130293)
INSERT INTO order_pallet VALUES (655555, 130293)

Diesen Beitrag habe ich bereits gelesen: Rekursion verstehen

H
523 Beiträge seit 2008
vor 4 Jahren

Ich persönlich würde das gar nicht über eine einzige SQL-Abfrage, sondern über c#-Routinen mit mehreren SQL-Abfragen lösen. Das macht es in der Regel einfacher zu lesen (für sich selber und ggf. auch für andere Entwickler).

16.806 Beiträge seit 2008
vor 4 Jahren

Es liest sich zumindest so, dass Du in eine endlose Rekursion kommen kannst.

..
Für alle gefundenen Vorgänge alle Paletten finden
..
Für alle gefundenen Paletten alle Vorgänge finden
..

Ohne ein definiertes Ende, sofern ich das richtig verstehe, wirst Du keine Lösung erhalten.

R
RayYago Themenstarter:in
19 Beiträge seit 2019
vor 4 Jahren

Das Ende wäre doch dann erreicht sobald keine weiteren Vorgänge/Paletten mehr gefunden werden. Oder verstehe ich dich falsch?

3.170 Beiträge seit 2006
vor 4 Jahren

Hallo,

die Endlosrekursion kann (relativ) leicht entstehen, wenn Du z.B. auf einer - irgendwo im Baum durch Ermitteln der zugehörigen Paletten zu einem gefundenen Sub-Vorgang - gefundenen Palette nochmal auf einen bereits gefundenen Vorgang stößt (z.B. den ursprünglichen, über den Du in die Suche startest).
Du musst also irgendwie sicherstellen, dass bereits gefundene Vorgänge oder Paletten nicht nochmal berücksichtigt werden.
Entweder Du setzt mehrere SQL-Queries ab, die diese Logik abbilden, oder Du versuchst, die Logik in einer Stored Procedure zu implementieren.
Das in einem einzigen SQL-Statement abzubilden, wird jedenfalls ziemlich schwierig (wenn nicht unmöglich) werden.

Gruß, MarsStein

Non quia difficilia sunt, non audemus, sed quia non audemus, difficilia sunt! - Seneca