Rekursive Abfragen

[schleudertrauma]

Moin zusammen,

ich bin zwar nicht wirklich neu in Sachen SQL aber bisher ging es bei mir nicht wirklich über einfaches SELECT und UPDATE (vielleicht auch ein paar Unterabfragen) hinaus, wenn ich mit Daten arbeiten wollte. Deswegen ist mir mein aktuelles Problem auch recht neu und ich weiss nicht, wie ich herangehen soll.

Das ganze soll später auf einem SQL Server 2005 laufen, TSQL wäre also sinnvoll. Nun aber zu meiner Aufgabe.

Ich habe eine Tabelle, die vereinfacht wie folgt aussieht:

Material Komponente Menge
-----------------------------------------
Material1 Material23 20
Material1 Material22 20
Material1 Material89 60

Material2 Material78 25
Material2 Material23 25
Material2 Material1 40
Material2 Material3 10

Material3 Material22 25
Material3 Material45 25
Material3 Material64 25
Material3 Material14 25

Material4 Material23 40
Material4 Material3 30
Material4 Material2 30


Es handelt sich quasi um eine Rezeptur. Material1 besteht zu 20% aus Material23, zu 20% aus Material22 und zu 60% aus Material89.

Diese Materialien finden sich ebenfalls in der selben Tabelle. Dies sieht man bei Material2. 2 der Inhaltsstoffe finden sich auch in der Tabelle, nämlich die Materialien 1 und 3.

Nun möchte ich eine Abfrage schreiben, die mir die verwendeten Komponenten und deren Menge bis auf die letzte Ebene herunterbricht. Ausgangspunkt der Abfrage soll eine Liste der benutzten Materialien sein.

Ich sage z.B.: Gibt mir alles, was Du zu Material2 findest.

Das sollte dann wie folgt aussehen:

Material Menge
------------------------------
Material78 25
Material23 25

Material23 8
Material22 8
Material89 24

Material22 2,5
Material45 2,5
Material64 2,5
Material14 2,5


Die Inhaltsstoffe und deren Menge wurden also bis auf die 2. Ebene runtergebrochen. Die zweite Ebene muss natürlich nicht die letzte Ebene sein denn diese Materialien können ja ihrerseits wieder aus mehreren Inhaltsstoffen zusammengesetzt sein. Ziel des ganzen ist es, aus einer Liste von Materialen eine Liste der benötigten Materialien der untersten Stufe zu bekommen. Es handelt sich also um eine Art der Rohstoffberechnung.

Ich habe zwar eine grobe Vorstellung, dass ich das ganze mit einer Schleife lösen muss. Allerdings bleibe ich meistens stecken wenns ans Konkrete geht. Ich will ja nicht, dass mir jemand den Code vorbetet aber es wäre schön, wenn ich ein paar Denkansätze bekommen könnte, die sich zum aufbauen eignen.

Danke schonmal für jede Idee. :-)

Gruß
Daniel

[Novi]

Hi,

es gibt glaube ich auch die Möglichkeit while-Schleifen in MySql zu verwenden. Damit kenne ich mich jedoch nicht aus.

Pseudocode

x = SELECT Komponente WHERE Material = 'Material2'
WHILE ( x enthält Elemente)
   SET x = SELECT Komponente WHERE Material = IN(x)
END WHILE;

Ansonsten wird es wohl die einfachste Möglichkeit sein dies per Php zu lösen.

[O. Helper]

Hallo schleudertrauma,

PHP, Schleife, Cursor ... pfft.

Ab Sql Server 2005 gibt es CTE (Common Table Expressions), die man auch rekursive anwenden kann.
[highlight=SQL]SET NOCOUNT ON;
CREATE TABLE #mat
(Material varchar(20)
,Komponente varchar(20)
,Menge int)
INSERT INTO #mat VALUES ('Material1', 'Material23', 20);
INSERT INTO #mat VALUES ('Material1', 'Material22', 20);
INSERT INTO #mat VALUES ('Material1', 'Material89', 60);
INSERT INTO #mat VALUES ('Material2', 'Material78', 25);
INSERT INTO #mat VALUES ('Material2', 'Material23', 25);
INSERT INTO #mat VALUES ('Material2', 'Material1', 40);
INSERT INTO #mat VALUES ('Material2', 'Material3', 10);
INSERT INTO #mat VALUES ('Material3', 'Material22', 25);
INSERT INTO #mat VALUES ('Material3', 'Material45', 25);
INSERT INTO #mat VALUES ('Material3', 'Material64', 25);
INSERT INTO #mat VALUES ('Material3', 'Material14', 25);
INSERT INTO #mat VALUES ('Material4', 'Material23', 40);
INSERT INTO #mat VALUES ('Material4', 'Material3', 30);
INSERT INTO #mat VALUES ('Material4', 'Material2', 30);
GO

WITH Recur (Material, Komponente, Menge) AS
(SELECT Material, Komponente, Menge
FROM #mat
WHERE Material = 'Material2'
UNION ALL
SELECT #mat.Komponente, #mat.Komponente, #mat.Menge
FROM Recur
INNER JOIN
#mat
ON Recur.Komponente = #mat.Material
)
SELECT Material, Komponente, Menge,
CONVERT(money, Menge)
/ CONVERT(money, ISNULL((SELECT SUM(Menge) FROM Recur), 0))
FROM Recur

GO
DROP TABLE #mat[/highlight]

Übrigens, Deine Beispiel-Ergebnisse sind etwas falsch.
Richtig:
[highlight=code]Material Komponente Menge
-------------------- -------------------- ----------- ---------------------
Material2 Material78 25 0,0833
Material2 Material23 25 0,0833
Material2 Material1 40 0,1333
Material2 Material3 10 0,0333
Material22 Material22 25 0,0833
Material45 Material45 25 0,0833
Material64 Material64 25 0,0833
Material14 Material14 25 0,0833
Material23 Material23 20 0,0666
Material22 Material22 20 0,0666
Material89 Material89 60 0,20[/highlight]

[schleudertrauma]

Suuupergeil!

Nach ein bisschen rumprobieren ist mir jetzt das Prinzip klar. Ich habs für meine Zwecke angewendet, ein bisschen erweitert und angepasst. Das einzige Problem ist, dass die Berechnung der Zusammensetzung im letzten Feld nicht hin kommt. Man darf nicht mit der Summe aller Zeilen arbeiten da manche Materialien bedingt durch mehrfaches Runterbrechen ja mehrmals vorkommen. Damit ist die Gesamtsumme größer als 100. Man darf also nur mit dem jeweiligen Ende der Kette arbeiten.

Aber das bekomme ich auch allein hin.

Vielen Dank für den Denkanstoß, hat mir viele neue Einsichten beschert.