SQL Query Rekursion

[RayYago]

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:


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.

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)

​

Hat jemand vielleicht einen Tipp oder eine Idee wie ich das lösen könnte?

Beispiel statisches SQL (ist glaube ich ein wenig verkehrt bzw. nicht ganz korrekt):

SELECT secondRecursion.order_nb, secondRecursion.pallet_nb
FROM (
SELECT firstRecursion.order_nb, firstRecursion.pallet_nb
FROM (
select order_nb, pallet_nb
from order_pallet
where order_nb = 655843) as root
INNER JOIN order_pallet firstRecursion on root.pallet_nb = firstRecursion.pallet_nb) firstRecursion
INNER JOIN order_pallet secondRecursion on firstRecursion.order_nb = secondRecursion.order_nb


Das SQL ist natürlich aber nicht flexible und ich bekomme es einfach nicht hin... ganzen Tag darauf vergeudet und gebe langsam auf.

 

[akretschmer]

Understanding SQL Server Recursive CTE By Practical Examples

 

[RayYago]

Danke für den Link, das hattes ich bereits schon gelesen. 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
......

Dieser Switch von Paletten finden und Vorgänge finden treibt mich in den Wahnsinn.

Ich bekomme die Verknüpfung einfach nicht richtig hin so das dieser Wechsel zustande kommt.
Mein letzter Versuch war:

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


Aber das zweite Select passt nicht weil ich nicht herausfinde wie ich den wechsel von Vorgang und Palette suchen in der CTE darstelle

 

[ukulele]

Es findet eigentlich kein Wechsel statt sondern nur ein Join zwischen Order und Palette. Der Join findet alle Paletten zur Order, das ist dann wie je ein Datensatz. Diese Datensätze werden dann rekursiv mit anderen Order/Paletten-Daten durchlaufen.

In deiner Tabelle "cte" wird ja schon gejoint. Vielleicht kapselst du das noch für besseres Verständnis. Hier mal als pseudo Code:

WITH tabelle AS ( SELECT * FROM order INNER JOIN pallet ON ... ), cte AS ( SELECT * FROM tabelle UNION ALL SELECT * FROM cte INNER JOIN tabelle ON ... ) SELECT * FROM cte