MSSQL Rekursionslevel bestimmen

[Norbert987]

Hallo Zusammen,

ich habe ein Problem mit SQL. Ich habe eine Tabelle "Genre" welche wie folgt aussieht:

Code:

ID   GenreBez       GenreSubID
-----------------------------------
1   Music           NULL
2   Rock            1
3   Pop             1
4   Electronica     1
5   Jazz            1
6   Hard Rock       2
7   Mainstream      2
8   Alternative     2
...

Die Tabelle ist nun folgendermaasen zu verstehen: 'Rock' und 'Pop' haben als SubGenre 'Music', 'Hard Rock' und 'Mainstream' haben als SubGenre 'Rock'. Ist es möglich, die Entfernungen rauszufinden? Also 'Music' <-> 'Rock' hat die Entfernung 1, 'Hard Rock' <-> 'Music' hat die Entfernung 2 etc.? Wie kann man das in eine neue Spalte machen, hat da jemand einen Tipp für mich?

Vielen Dank im Vorraus, Norbert

[generic]

Informiere dich mal über Nested Sets.

[Bummi]

Oder Du nimmst eine Spalte Level mit auf...

[rollstuhlfahrer]

Also per SQL sollte das nicht möglich sein. Was du alternativ machen kannst, ist das ganze in einen TreeView zu importieren und darüber die Abstände zu berechnen. Du musst dir das dann wie ein Baumdiagramm vorstellen.

Zu dem sollte: Ich denke mal nicht, dass es möglich ist, eine Rekursion mit unbestimmter Tiefe durchzuführen und dann die Rekursionstiefe zu ermitteln. Das ist selbst bei Windows-Programmen nur mit Mit-Zähl-Variablen möglich.

Bernhard

[Bummi]

@rollstuhlfahrer
man kann so eine Prozedur schon schreiben:

Code:

Create Procedure P_GetTreeDist(@ID1 int,@ID2 int) As
--201011 by Thomas Wassermann
Declare @count int
Declare @dist int

Select Cast(@ID1 as int) as ID,0 as Dist 
into #tmp1

Select Cast(@ID2 as int) as ID,0 as Dist 
into #tmp2

select @Count=0
Select @dist=1
While @Count<(Select Count(*) from #tmp1)
   begin
   Select @Count=(Select Count(*) from #tmp1)
   insert into #tmp1 Select Cast(refparent as int),@dist from  [new_tbl_categories]
   where internalid in (Select ID from #tmp1) and refparent not in (Select ID from #tmp1)
   Select @dist = @dist + 1
   end

select @Count=0
Select @dist=1
While @Count<(Select Count(*) from #tmp2)
   begin
   Select @Count=(Select Count(*) from #tmp2)
   insert into #tmp2 Select Cast(refparent as int),@dist from  [new_tbl_categories]
   where internalid in (Select ID from #tmp2) and refparent not in (Select ID from #tmp2)
   Select @dist = @dist + 1
   end

Select Min(#tmp1.Dist+#tmp2.Dist) as Distance from #tmp1 join #tmp2 on #tmp1.ID = #tmp2.ID

Drop Table #tmp1
Drop Table #tmp2

[Norbert987]

Vielen Dank für eure Anregungen. Die Funktion sieht ja wirklich passend aus Ich habe sie reinkompiliert und würde sie nun so nutzen:

Code:

select og.genreid as OGenreID,
       og.Bezeichnung as OGenreBezeichnung,
       ug.genreid as UGenreID,
       ug.Bezeichnung as UGenreBezeichnung,
       P_GetTreeDist(og.genreid, ug.genreid) as Entfernung
  from genre og,
       genre ug
 where Entfernung > 0

aber da mache ich wohl noch einen Fehler (''P_GetTreeDist' wird nicht als Name einer integrierten Funktion erkannt.'). Wie sieht solch ein Aufruf richtig aus?

[Bummi]

Das ist eine Procedure, wenn Du sie wie beschrieben nutzen willst mußt Du es zu einer FunctionTable umschreiben die Du dazujoinst, Function geht wegen der Temporärtabellen nicht.
Functiontables gibt es 2005 aufwärts, bei Dir also machbar.

EDIT:
Du hast schon gesehen daß ich beim Aufbau auf eine ganz andere Tabelle zugreife, Du also eh umbauen musst?

[omata]

klick

Zitat von Bummi:

Functiontables gibt es 2005 aufwärts, bei Dir also machbar.

Das geht ab 2000.

[Norbert987]

Vielen Dank an euch beide, da werde ich mich mal einlesen.

Schönen Abend noch

[Bummi]

Ich habe mir den Spass gemacht

Kannst Du jetzt über

Code:

 
Select a,b,c,(Select Dist from F_GetTreeDist(x,y)) as Dist
from Irgendwas

aufrufen..

Code:

Create FUNCTION F_GetTreeDist(@ID1 int,@ID2 int) 
--201011 by Thomas Wassermann
RETURNS 
@tab TABLE 
(
Ref int,
ID int,
dist int
)
AS
BEGIN
Declare @count int
Declare @dist int
Declare @ref int


Insert into @tab (Ref,ID,Dist) Values (1,@ID1,0)

Insert into @tab (Ref,ID,Dist) Values (2,@ID2,0)

Select @ref = 1

select @Count=0
Select @dist=1
While @Count<(Select Count(*) from @tab where Ref=@ref)
   begin
   Select @Count=(Select Count(*) from @tab where Ref=@ref)
   insert into @tab Select @ref,Cast(refparent as int),@dist from  [new_tbl_categories]
   where internalid in (Select ID from @tab where Ref=@ref) and refparent not in (Select ID from @tab where Ref=@ref)
   Select @dist = @dist + 1
   end

select @Count=0
Select @dist=1
Select @ref = 2


While @Count<(Select Count(*) from @tab where Ref=@ref)
   begin
   Select @Count=(Select Count(*) from @tab where Ref=@ref)
   insert into @tab Select @ref,Cast(refparent as int),@dist from  [new_tbl_categories]
   where internalid in (Select ID from @tab where Ref=@ref) and refparent not in (Select ID from @tab where Ref=@ref)
   Select @dist = @dist + 1
   end

Insert Into @Tab (Ref,Dist)
Select 3,  Min(t1.Dist+t2.Dist) 
from @tab t1 join @tab t2 on t1.ID = t2.ID
where t1.Ref=1 and t2.Ref=2

Delete @tab where Ref<3
   RETURN 
END
GO