Recursive Query

[julius213]

Ich habe folgendes Problem ich will eine Recursive Query erstellen die mir die Anzahl der Vorprodukte angibt. Tabelle siehe Anhang
Also das Resultat soll im Prinzip sein :
ID| Anzahl der Vorprodukte
1 | 2
2 | 0
3 | 0
4 | 1
5 | 0
6 | 0
7 | 0
Problem ich bekomme es nur hin das mir SQL ausgibt wer das Vorprodukt ist..
Hat jemand eine Ahnung wie man so eine Abfrage schreibt ?.....

 

[akretschmer]

Deine gezeigte Ergebnissmenge scheint mir nicht so ganz zu Deinen Daten zu passen. Egal, mein Kollege hat das vor vielen Jahren mal gezeigt, hier der Link: Spaß mit PostgreSQL , Folie 12.

 

[ukulele]

CREATE TABLE test(
   ID INT NOT NULL,
   Bauteil VARCHAR(10) NOT NULL,
   Vorprodukt INT NULL
   );

INSERT INTO test(ID,Bauteil,Vorprodukt) VALUES(1,'A',NULL);
INSERT INTO test(ID,Bauteil,Vorprodukt) VALUES(2,'B',1);
INSERT INTO test(ID,Bauteil,Vorprodukt) VALUES(3,'C',1);
INSERT INTO test(ID,Bauteil,Vorprodukt) VALUES(4,'D',NULL);
INSERT INTO test(ID,Bauteil,Vorprodukt) VALUES(5,'E',4);
INSERT INTO test(ID,Bauteil,Vorprodukt) VALUES(6,'F',1);
INSERT INTO test(ID,Bauteil,Vorprodukt) VALUES(7,'G',6);

WITH t3 AS (
   SELECT   t1.ID,
       t1.Vorprodukt,
       t1.Bauteil,
       0 AS [Level]
   FROM   test t1
   WHERE   t1.Vorprodukt IS NULL
  UNION ALL
   SELECT   t2.ID,
       t2.Vorprodukt,
       t2.Bauteil,
       t3.[Level] + 1
   FROM   test t2
   JOIN   t3
   ON     t2.Vorprodukt = t3.ID )
SELECT   t3.Bauteil,
     t3.[Level],
     t3.Vorprodukt,
     t3.ID
FROM   t3
ORDER BY t3.ID

Mit WITH läßt sich das in jedem Fall vernünftig umsetzen, hier mal ein Beispiel. Level müsste dem entsprechen was du suchst. Quelle ist dieser Code:

WITH c AS (
   SELECT   billID,
       parentBillID,
       descr,
       0 AS [level]
   FROM   dbo.bill b
   WHERE   b.parentBillID IS NULL
  UNION ALL
   SELECT   b.billID,
       b.parentBillID,
       b.descr,
       [level] + 1
   FROM   dbo.bill b
   JOIN   c
   ON     b.parentBillID = c.billID )
SELECT   descr,
     [level],
     billID,
     parentBillID AS bill
FROM   c

von http://msdn.microsoft.com/de-de/magazine/cc794278.aspx

 

[julius213]

WITH t3 AS (
SELECT t1.ID,
t1.Vorprodukt,
t1.Bauteil,
0 AS [Level]
FROM test t1
WHERE t1.Vorprodukt IS NULL
UNION ALL
SELECT t2.ID,
t2.Vorprodukt,
t2.Bauteil,
t3.[Level] + 1
FROM test t2
JOIN t3
ON t2.Vorprodukt = t3.ID )
SELECT t3.Bauteil,
t3.[Level],
t3.Vorprodukt,
t3.ID
FROM t3
ORDER BY t3.ID

das passt fast nur geht der Code genau anders herum ^^ also der sagt mir wie viele Produkte danach kommen in prinzip ich wollte aber wissen wieviele Produkte vorher kommen ^^

 

[ukulele]

Ja das kann man auch umdrehen aber das Prinzip bleibt gleich und so wars jetzt auf die Schnelle einfacher. Erweiter mal erst etwas deine Tests und probier mal das passend umzustellen.

 

[julius213]

Ja das kann man auch umdrehen aber das Prinzip bleibt gleich und so wars jetzt auf die Schnelle einfacher. Erweiter mal erst etwas deine Tests und probier mal das passend umzustellen.

in welchen Bereich muss ich da den umstellen ? weil der anfang

WITH t3 AS (
   SELECT   t1.ID,
       t1.Vorprodukt,
       t1.Bauteil,
       0 AS [Level]
   FROM   test t1
   WHERE   t1.Vorprodukt IS NULL
  UNION ALL

müsste ja so bleiben oder ?
habe schon rumprobiert aber iwie kommt entweder fehler oder als level immer null ^^

 

[ukulele]

Eigentlich passt das doch auch:

WITH t3 AS (
   SELECT   t1.ID,
       t1.Vorprodukt,
       t1.Bauteil,
       cast(t1.Bauteil AS VARCHAR(1000)) AS Pfad,
       0 AS Anzahl_Vorprodukte
   FROM   test t1
   WHERE   t1.Vorprodukt IS NULL
  UNION ALL
   SELECT   t2.ID,
       t2.Vorprodukt,
       t2.Bauteil,
       cast(cast(t2.Bauteil AS VARCHAR(1000)) + '/' + t3.Pfad AS VARCHAR(1000)) AS Pfad,
       t3.Anzahl_Vorprodukte + 1
   FROM   test t2
   JOIN   t3
   ON     t2.Vorprodukt = t3.ID )
SELECT   t3.Bauteil,
     t3.Anzahl_Vorprodukte,
     t3.Vorprodukt,
     t3.Pfad,
     t3.ID
FROM   t3
ORDER BY t3.ID

 

[julius213]

Eigentlich passt das doch auch:

WITH t3 AS (
   SELECT   t1.ID,
       t1.Vorprodukt,
       t1.Bauteil,
       cast(t1.Bauteil AS VARCHAR(1000)) AS Pfad,
       0 AS Anzahl_Vorprodukte
   FROM   test t1
   WHERE   t1.Vorprodukt IS NULL
  UNION ALL
   SELECT   t2.ID,
       t2.Vorprodukt,
       t2.Bauteil,
       cast(cast(t2.Bauteil AS VARCHAR(1000)) + '/' + t3.Pfad AS VARCHAR(1000)) AS Pfad,
       t3.Anzahl_Vorprodukte + 1
   FROM   test t2
   JOIN   t3
   ON     t2.Vorprodukt = t3.ID )
SELECT   t3.Bauteil,
     t3.Anzahl_Vorprodukte,
     t3.Vorprodukt,
     t3.Pfad,
     t3.ID
FROM   t3
ORDER BY t3.ID

Im Prinzip ja, leider ist das Ergebnisse ja dann immer noch nicht im ERgebniss einfach Angezeigt bekomme, die Bezeichnung des Vorproduktes und daneben die Anzahl der Vorprodukte. Weil das ERgebniss ist halt immer noch das mir Angezeigt wird wieviele Nachfolge Produkte ein Vorprodukt hat.....

 

[ukulele]

Du kannst mit einem LEFT JOIN natürlich noch weitere Informationen holen:

WITH t3 AS (
   SELECT   t1.ID,
       t1.Vorprodukt,
       t1.Bauteil,
       0 AS Anzahl_der_Vorprodukte
   FROM   test t1
   WHERE   t1.Vorprodukt IS NULL
  UNION ALL
   SELECT   t2.ID,
       t2.Vorprodukt,
       t2.Bauteil,
       t3.Anzahl_der_Vorprodukte + 1
   FROM   test t2
   JOIN   t3
   ON     t2.Vorprodukt = t3.ID )
SELECT   t3.ID,
     t.Bauteil AS Bezeichnung_des_Vorproduktes,
     t3.Anzahl_der_Vorprodukte
FROM   t3
LEFT JOIN test t
ON     t3.Vorprodukt = t.ID

Die Anzahl der Vorprodukte bezieht sich natürlich auf in Reihe befindlichen Vorprodukte. Also Bauteil 7 hat Vorprodukt 6, Bauteil 6 hat Vorprodukt 1 also hat Bauteil 7 in Summe 2 Vorprodukte. (Das erste Vorprukt wird als "Bezeichnung_des_Vorproduktes" ausgegeben.)

Oder suchst du etwas wie Bauteil 1 ist das Vorprodukt von 3 anderen Bauteilen?

 

[julius213]

Ich möchte gerne wissen im Prinzip aus wievielen Vorprodukt Ebenen Teil 1 Besteht sprich
Bauteil 1 Besteht aus 2,3,6 und 6 aus Bauteil 7. Somit soll das Ergebniss der Query so sein das es mir Sagt Bauteil 1 und Ebenen Anzahl 2
Wohin gegen bei Bauteil 6 nur 1 Ebene hat weil ja nur 7 darunter steht.
und Bauteil 5 auch nur 1 Ebene unter sich hat

 

[ukulele]

Genau das kommt bei Anzahl_der_Vorprodukte bei mir raus.

 

[julius213]

Oder suchst du etwas wie Bauteil 1 ist das Vorprodukt von 3 anderen Bauteilen?

Genau das Suche ich ! aus wievielen Ebenen von Vorprodukten besteht Bauteil 1
in den Beispiel wären es ja 2 Ebenen weil -> Ebene 1 aus 2,3,6 besteht und weil Bauteil 6 aus Bauteil 7 noch besteht.

 

[akretschmer]

also sowas?

test=*# select * from bauteile ;
 id | bauteil | vorprodukt
----+---------+------------
  1 | a  |
  2 | b  |  1
  3 | c  |  1
  4 | d  |
  5 | e  |  4
  6 | f  |  1
  7 | g  |  6
(7 rows)

test=*# select vorprodukt, array_agg(distinct id) verwendet_in from (with recursive r as (select b.id, b.bauteil, b.vorprodukt, 1::int as ebenen from bauteile b where vorprodukt is not NULL union all select b.id, b.bauteil, b.vorprodukt, r.ebenen+1 from bauteile b join r on b.vorprodukt=r.id) select * from r) foo group by vorprodukt order by vorprodukt;
 vorprodukt | verwendet_in
------------+--------------
  1 | {2,3,6}
  4 | {5}
  6 | {7}
(3 rows)

 

[julius213]

Ne so :
vorprodukt | Anzahl der Produktebenen
------------+--------------
1 | 2 -> da ja 2,3,6 auf einer ebene liegen und 6 noch eine unterebene hat
4 | 1
6 | 1

 

[akretschmer]

vorprodukt | Anzahl der Produktebenen
------------+--------------
1 | 2 -> da ja 2,3,6 auf einer ebene liegen und 6 noch eine unterebene hat
4 | 1
6 | 1

Dann muß aber die 2 bei 6 und nicht bei 1 sein, oder?

test=*# select * from bauteile ;
 id | bauteil | vorprodukt
----+---------+------------
  1 | a  |
  2 | b  |  1
  3 | c  |  1
  4 | d  |
  5 | e  |  4
  6 | f  |  1
  7 | g  |  6
(7 rows)

test=*# select vorprodukt, max(tiefe) from (with recursive r as (select b.id, b.bauteil, b.vorprodukt, 1::int as tiefe from bauteile b where vorprodukt is not NULL union all select b.id, b.bauteil, b.vorprodukt, r.tiefe+1 from bauteile b join r on b.vorprodukt=r.id) select * from r) foo group by vorprodukt;
 vorprodukt | max
------------+-----
  4 |  1
  1 |  1
  6 |  2
(3 rows)