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:

Zitat von Bummi (Beitrag 1063144)

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

[Bummi]

@omata

mein geht ab 2005 bezog sich auf Funtiontables, die Prozedur von mir geht freilich auch ab 2000, unter Deinem Verweis fand ich auch nur Prozeduren.

[Norbert987]

@Bummi: was soll ich dazu sagen? Vielen, vielen Dank! Leider meldet er folgenden Fehler: 'Ungültiger Objektname 'new_tbl_categories'.', wenn ich es wie von dir beschrieben aufrufe. Hast du da eine Idee?

Mein Versuch bis jetzt ist eher mau:

Code:

create function genreDist(@UGenreId int,@OGenreId int)
RETURNS int
as
begin
   declare @dist int
   declare @curr int
    set @dist = 0
   set @curr = @UGenreId

   while @curr != @UGenreId
    begin
      set @dist = @dist+1

      select @curr = (select obergenre
                    from genre
                   where genreid = @curr)
    end

   return @dist
end;

es kompiliert nichtmal und Errorhandling wäre natürlich auch angebracht^^

[Bummi]

wie gesagt ich hatte auf einer vorhanden Tabelle getestet

3 mal Suchen und Ersetzen:

new_tbl_categories >> Genre
internalid >> ID
refparent >> GenreSubID

BTW: der Algo funktioniert folgendermassen gegf. anpassen, er sucht den ersten gemeinsamen Vorfahr und summiert die Abstände der beiden Knoten zu zu diesem

[Norbert987]

Oh Klasse vielen, vielen Dank! Nun läufts. :)

[borwin]

Noch ein Gedanke.
Ab MSSQL 2005 geht auch folgendes. Sichwort Common Table Expressions (CTE)

Code:

WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name

Siehe hierzu bei MS CTE
oder auch CTE Beispiele
Hier ist auch ein Beispiel zur Ermittlung des Levels im SQL
Vorteil keine Funktionen und auch noch schneller.

SQL Syntax findet man auch bei Firebird


Gruß BORWIN

[Bummi]

@borwin

vielen Dank ....