In sich verschachtelte Stücklise mit T-SQL abfragen

Question

Hallo,

Ich habe Produkte, die aus Teilen bestehen, welche teilw. wieder aus Teilen bestehen usw. Das geht aber nicht unendlich, praktisch ist nach 3 spätestens 4 Leveln Schluss. Wie kann ich (z. B. mit fix definierter Anzahl von Leveln) eine Abfrage erstellen, in denen nur die Teile auf der untersten Ebene vorkommen? (wenn möglich in einer T-SQL-Abfrage mit Unterabfragen, sonst auch mit Prozeduren)

So sind meine Daten gespeichert, wenn im dritten Feld "1" steht, dann besteht das Teil aus anderen Teilen. Produkt X besteht also aus der Gruppe B, welche u. A. aus Gruppe F besteht. Die einzelnen Teile können in allen möglichen Produkten X, Y, Z verbaut werden:

Produkt, Teil, Stückliste

X A
X B 1
X C
B D
B E
B F 1
F G
F H

So soll das Abfrageergebnis aussehen:

Produkt, Teil, Stückliste

X A
X C
X D
X E
X G
X H
usw:
Y A
Y D
Y H
Z C
Z G
Z H
usw.

Ich weiss nicht, wo ich anfangen soll, ich bitte um Hilfe bei Erstellung eines T-SQL-Codes.

lg
arno

 

Answer 1

hi Arno,

Ich weiss nicht, wo ich anfangen soll, ich bitte um Hilfe bei Erstellung eines T-SQL-Codes.

Hier:

http://msdn.microsoft.com/en-us/library/ms186243.aspx

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 2

Vielen Dank!
arno

Answer 3

Hallo,

ich muss doch noch einmal nachfragen:

Wie kann ich alle Ergebniszeilen mit dem Startelement kennzeichnen? Dh. ich erstelle eine Liste mit allen Produkten mit allen deren Komponenten/Teilen. Wie bekomme ich das Startelement der Rekursion (=Mother) in ein Feld "Produkt" des Ergebnisses?

Hier ist mein Beispiel für die Artikel (Mother) w, x, y, z, die aus den Teilen (HasChild) a bis m bestehen:

MOTHER  HASCHILD
a     null
b     a
b     d
b     e
b     f
c     null
d     null
e     null
f     g
f     h
g     null
h     null
k     m
m     null
w     null
x     a
x     b
x     c
y     a
y     g
y     k
z     a
z     e

Meine Abfrage:

WITH myrec (stufe, mother, haschild) as
( SELECT 1 stufe, mother, haschild from dwh.testrec WHERE mother >= 'w'
UNION ALL SELECT b.stufe + 1, a.mother, a.haschild from dwh.testrec a
INNER JOIN myrec b ON b.haschild=a.mother
WHERE b.stufe < 20)

SELECT * from myrec (für alle Daten der Tabelle) oder
select * from myrec where haschild is not null (für ausschliesslich die untersten Elemente/Teile)

STUFE  MOTHER  HASCHILD
1     w     null
1     x     a
1     x     b
1     x     c
2     a     null
2     b     d
2     b     e
2     b     f
2     b     a
3     a     null
3     e     null
3     d     null
3     f     g
3     f     h
4     g     null
4     h     null
2     c     null
1     y     a
1     y     g
1     y     k
2     a     null
2     g     null
2     k     m
3     m     null
1     z     a
1     z     e
2     a     null
2     e     null

Wie man sieht kommt Teil "a" öfters vor, zu welcher Mutter gehört der? Das sollte in der ersten Spalte stehen, nur wie bekomme ich die in das T-SQL-Statement (also w, x, y od. z)?

So sollte also das Abfrageergebnis der Rohstoffe für die Produkte x und y aussehen: (a ist mit Absicht doppelt)

PRODUKT  STUFE  MUTTER  HATKIND
x     2     a     null
x     3     a     null
x     3     d     null
x     3     e     null
x     4     g     null
x     4     h     null
x     2     c     null
y     2     g     null
y     3     m     null

lg
a.s.

Answer 4

hi Arno,

wenn ich dich richtig verstehe:

USE [tempdb] ;
GO

IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[BOM]')
                    AND type IN ( N'U' ) )
    DROP TABLE [dbo].[BOM] ;

CREATE TABLE [dbo].[BOM]
    (
      [Mother] VARCHAR(255),
      [Child] VARCHAR(255)
    ) ;

INSERT  INTO [dbo].[BOM]
VALUES  ( 'a', NULL ),
        ( 'b', 'a' ),
        ( 'b', 'd' ),
        ( 'b', 'e' ),
        ( 'b', 'f' ),
        ( 'c', NULL ),
        ( 'd', NULL ),
        ( 'e', NULL ),
        ( 'f', 'g' ),
        ( 'f', 'h' ),
        ( 'g', NULL ),
        ( 'h', NULL ),
        ( 'k', 'm' ),
        ( 'm', NULL ),
        ( 'w', NULL ),
        ( 'x', 'a' ),
        ( 'x', 'b' ),
        ( 'x', 'c' ),
        ( 'y', 'a' ),
        ( 'y', 'g' ),
        ( 'y', 'k' ),
        ( 'z', 'a' ),
        ( 'z', 'e' ) ;
GO

WITH    cte ( [Level], [Root], [Mother], [Child] )
          AS ( SELECT   1,
                        B.[Mother],
                        B.[Mother],
                        B.[Child]
               FROM     [dbo].[BOM] B
               WHERE    NOT B.[Mother] IN ( SELECT  [Child]
                                            FROM    [dbo].[BOM]
                                            WHERE   NOT [Child] IS NULL )
               UNION ALL
               SELECT   C.[Level] + 1,
                        C.[Root],
                        B.[Mother],
                        B.[Child]
               FROM     [dbo].[BOM] B
                        INNER JOIN cte C ON C.Child = B.Mother
             )
    SELECT  *
    FROM    cte ;

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 5

>wenn ich dich richtig verstehe

ja, du verstehst mich :)

In meinen Worten, MYROOT ist gesucht (alles wird abgerufen, einschränken auf die letzten Elemente kann man später immer noch):

WITH myrec (stufe, mother, MYROOT, haschild) as
( SELECT 1 stufe, mother, MOTHER, haschild from dwh.testrec WHERE mother >= 'w'
UNION ALL SELECT b.stufe + 1, a.mother, b.MYROOT, a.haschild from dwh.testrec a
INNER JOIN myrec b ON b.haschild=a.mother
WHERE b.stufe < 20)
SELECT * from myrec

lg
arno

Answer 6

hallo Yury,

nicht wirklich. Mit seinen Daten bekommst du:

[code]
mother  child
w    NULL
z    NULL
z    NULL
y    NULL
y    NULL
y    NULL
x    NULL
x    NULL
x    NULL
x    NULL
x    NULL
x    NULL
x    NULL
[code]

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 7

hallo Yury,

nicht wirklich. Mit seinen Daten bekommst du:

[code]
mother  child
w    NULL
z    NULL
z    NULL
y    NULL
y    NULL
y    NULL
x    NULL
x    NULL
x    NULL
x    NULL
x    NULL
x    NULL
x    NULL
[code]

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Vielleicht liegt es ja nur daran, dass es noch relativ früh an Morgen ist, aber ich sehe nicht den Beitrag, auf den sich diese Antwort bezieht.

Ich halte das Entfernen von fachlichen Beiträgen auf die es bereits Antworten gibt, gelinde gesagt, für unglücklich und nicht angebracht und bin eigentlich bisher davon ausgegangen, dass dies auch technisch nicht möglich ist.

Oder verpasse ich gerade etwas?

---

-- Frank Kalis Microsoft SQL Server MVP Webmaster: http://www.insidesql.org

Answer 8

hi Frank,

Ah, er wurde tatsächlich gelöscht. Interessantes Feature... in Zukunft also wieder TOFU?

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann

Answer 9

Hallo zusammen,
die Abfrage, die ich gepostet habe war nicht vollständig, deswegen habe ich meinen Beitrag (noch vor dem Stefans Antwort) gelöscht.

---

Gruß Yury

Answer 10

hi Yury,

das kann passieren, aber in einem solchen Fall ist Editieren sicher der bessere Weg.

---

Microsoft MVP Office Access
https://mvp.support.microsoft.com/profile/Stefan.Hoffmann