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
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).
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.
- performance is a feature -
Microsoft MVP - @Website - @AzureStuttgart - github.com/BenjaminAbt - Sustainable Code
Das Ende wäre doch dann erreicht sobald keine weiteren Vorgänge/Paletten mehr gefunden werden. Oder verstehe ich dich falsch?
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