Question

我在Oracle 9i数据库中建立了父母与子女的关系。

如:

parent | child  
1      | 2  
2      | 3
2      | 4
null   | 1
1      | 8

我有一个绝对的父母(如1个孩子),并且需要一份该父母所有子女的名单或结果。

Answer 1

采用SYS_CONNECY_BY_PATH,将使你完全分级:

SELECT SYS_CONNECT_BY_PATH(parent,  , ) "PATH" 
  FROM table 
 START WITH child = 1 
CONNECT BY PRIOR child = parent;

http://www.psoug.org/fer/linkby.html“rel=“nofollow noreferer”>here。

Answer 2

我们刚刚搬离了Oracle,但我为你写了这套程序,在服务器中(这种程序应当与CURSOR声明非常相似)。

CREATE PROCEDURE ShowRelationships
@parent AS int
AS
 PRINT  Parent =    + CAST(@parent AS varchar(3))

 DECLARE @child AS int;
 DECLARE cur_children CURSOR
 FOR
  SELECT child
  FROM PCREL
  WHERE parent = @parent;

 OPEN cur_children;
 FETCH NEXT FROM cur_children INTO @child;

 IF (@child IS NULL)
 BEGIN
  PRINT CAST(@parent AS varchar(3)) +   has no children... ;
 END

 WHILE @@FETCH_STATUS = 0
 BEGIN
  PRINT  Child =   + CAST(@child AS varchar(3))

  FETCH NEXT FROM cur_children INTO @child;
 END

 CLOSE cur_children;
 DEALLOCATE cur_children;


 SELECT TOP 1 @child = child
 FROM PCREL
 WHERE parent = @parent;

 EXECUTE ShowRelationships @child;

GO

友情链接