请高手们帮忙写一条检索树形数据的sql语句，多谢。

编号（主键1）        ID（主键2）        父编号
1000                 a                1001
1000                 b                1002
1001                 a                1003
1002                 a                1004
1003                 a                1004
1003                 b                1006
1004                 a                1006
1005                 a                1007
1006                 a                1007
……                ……               ……

我遇到的问题，所涉及的数据，大概可以简化成这个样子，要求根据一个编号，检索出他的父编号，父编号的父编号，…… 以此类推，直到编号列中不再有最后得到的父编号为止，希望用一条sql语句，完成检索工作，得到所有的编号，该怎样写？请高手们指点，多谢。

8 个解决方案

#1

以前写的一段程序,你可能有用
PROCEDURE DATA_QUERY IS
  TYPE T_QTY IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  R_QTY   T_QTY;
  D_BOM                EM_ITEM_BOM%ROWTYPE;
  V_RET_VALUE          VARCHAR2(100);
  
  CURSOR C_FATHER IS 
    SELECT COMP_QTY,RECORD_STATUS,CREATED_BY,CREATED_ON,UPDATED_BY,UPDATED_ON
      FROM EM_ITEM_BOM
        WHERE COMP=D_BOM.COMP 
          AND BCR_TYPE=D_BOM.BCR_TYPE
        AND ITEM_ID=D_BOM.ITEM_ID 
        AND ITEM_REV_11=NVL(D_BOM.ITEM_REV_11,' ') 
        AND ITEM_REV_12=NVL(D_BOM.ITEM_REV_12,' ') 
        AND ITEM_REV_13=NVL(D_BOM.ITEM_REV_13,' ');
  
  D_FATHER            C_FATHER%ROWTYPE;       
      
  CURSOR C_COMPONENT IS 
    SELECT COMP_QTY,COMP_ITEM,LPAD('.',LEVEL,'.')||COMP_ITEM ITEM_NAME,
           COMP_REV_11,COMP_REV_12,COMP_REV_13,RECORD_STATUS,CREATED_BY,
           CREATED_ON,UPDATED_BY,UPDATED_ON,COMP_UM,LEVEL
      FROM EM_ITEM_BOM
        --WHERE COMP=D_BOM.COMP 
          --AND BCR_TYPE=D_BOM.BCR_TYPE
        START WITH (ITEM_ID=D_BOM.ITEM_ID 
        AND COMP = D_BOM.COMP
        AND BCR_TYPE = D_BOM.BCR_TYPE
        AND ITEM_REV_11 = NVL(D_BOM.ITEM_REV_11,' ') 
        AND ITEM_REV_12 = NVL(D_BOM.ITEM_REV_12,' ')
        AND ITEM_REV_13 = NVL(D_BOM.ITEM_REV_13,' '))
        CONNECT BY PRIOR COMP = COMP   
        AND PRIOR COMP_ITEM   = ITEM_ID
        AND PRIOR BCR_TYPE    = BCR_TYPE
        AND PRIOR COMP_REV_11 = NVL(ITEM_REV_11,' ')
        AND PRIOR COMP_REV_12 = NVL(ITEM_REV_12,' ') 
        AND PRIOR COMP_REV_13 = NVL(ITEM_REV_13,' ');
  
  D_COMPONENT         C_COMPONENT%ROWTYPE;        
        
  
  CURSOR C_DESC IS
    SELECT ITEM_DESC
      FROM EM_ITEM
        WHERE ITEM_ID=D_COMPONENT.COMP_ITEM
          AND ITEM_REV_11=NVL(D_COMPONENT.COMP_REV_11,' ')
          AND ITEM_REV_12=NVL(D_COMPONENT.COMP_REV_12,' ')
          AND ITEM_REV_13=NVL(D_COMPONENT.COMP_REV_13,' ');

BEGIN
      
    --INPUT VALUES INTO D_BOM
    D_BOM.COMP:=NAME_IN('HEADER_BLK.ITEM1');
    D_BOM.BCR_TYPE:=NAME_IN('HEADER_BLK.ITEM3');
    D_BOM.ITEM_ID:=NAME_IN('HEADER_BLK.ITEM_ID');

   D_BOM.ITEM_REV_11:=NAME_IN('HEADER_BLK.ITEM_REV_11');
   D_BOM.ITEM_REV_12:=NAME_IN('HEADER_BLK.ITEM_REV_12');
   D_BOM.ITEM_REV_13:=NAME_IN('HEADER_BLK.ITEM_REV_13');

     GO_BLOCK('DATA_BLK');
     CLEAR_BLOCK;
     OPEN C_COMPONENT;
     FETCH C_COMPONENT INTO D_COMPONENT;
     IF C_COMPONENT%FOUND THEN
        FIRST_RECORD;
        OPEN C_FATHER;
        FETCH C_FATHER INTO D_FATHER;
        IF C_FATHER%FOUND THEN
           --INPUT FIRST RECORD OF DATA_BLK COPY FROM HEADER_BLK 
           COPY(NAME_IN('HEADER_BLK.ITEM16'),'DATA_BLK.QUANTITY');
           R_QTY(0) := NVL(NAME_IN('HEADER_BLK.ITEM16'),1);
           COPY(NAME_IN('HEADER_BLK.ITEM_ID'),'DATA_BLK.COMPONENT');          
           COPY(NAME_IN('HEADER_BLK.ITEM_REV_11'),'DATA_BLK.REV_1');
           COPY(NAME_IN('HEADER_BLK.ITEM_REV_12'),'DATA_BLK.REV_2');
           COPY(NAME_IN('HEADER_BLK.ITEM_REV_13'),'DATA_BLK.REV_3');
           COPY(NAME_IN('HEADER_BLK.ITEM32'),'DATA_BLK.ITEM_DESC');
           --INPUT FIRST RECORD OF DATA_BLK FROM D_FATHER
           COPY(D_FATHER.RECORD_STATUS,'DATA_BLK.RECORD_STATUS');
           COPY(D_FATHER.CREATED_BY,'DATA_BLK.CREATED_BY');
           COPY(D_FATHER.CREATED_ON,'DATA_BLK.CREATED_ON');
           COPY(D_FATHER.UPDATED_BY,'DATA_BLK.UPDATED_BY');
           COPY(D_FATHER.UPDATED_ON,'DATA_BLK.UPDATED_ON');
        END IF;
        CLOSE C_FATHER;
        LOOP 
           EXIT WHEN C_COMPONENT%NOTFOUND;
           NEXT_RECORD;
           --CHECK ITEM_DESC
           OPEN C_DESC;
           V_RET_VALUE := NULL;
           FETCH C_DESC INTO V_RET_VALUE;
           COPY(V_RET_VALUE,'DATA_BLK.ITEM_DESC');
           CLOSE C_DESC;
           --BREAK;
           R_QTY(D_COMPONENT.LEVEL) := D_COMPONENT.COMP_QTY * R_QTY(D_COMPONENT.LEVEL - 1);
           COPY(R_QTY(D_COMPONENT.LEVEL),'DATA_BLK.QUANTITY');
           COPY(D_COMPONENT.ITEM_NAME,'DATA_BLK.COMPONENT');          
           COPY(D_COMPONENT.COMP_REV_11,'DATA_BLK.REV_1');
           COPY(D_COMPONENT.COMP_REV_12,'DATA_BLK.REV_2');
           COPY(D_COMPONENT.COMP_REV_13,'DATA_BLK.REV_3');
           COPY(D_COMPONENT.COMP_UM,'DATA_BLK.UM');
           COPY(D_COMPONENT.RECORD_STATUS,'DATA_BLK.RECORD_STATUS');
           COPY(D_COMPONENT.CREATED_BY,'DATA_BLK.CREATED_BY');
           COPY(D_COMPONENT.CREATED_ON,'DATA_BLK.CREATED_ON');
           COPY(D_COMPONENT.UPDATED_BY,'DATA_BLK.UPDATED_BY');
           COPY(D_COMPONENT.UPDATED_ON,'DATA_BLK.UPDATED_ON');
           FETCH C_COMPONENT INTO D_COMPONENT;
        END LOOP;        
        CLOSE C_COMPONENT;
        FIRST_RECORD;
     END IF;
     R_QTY.DELETE(0,R_QTY.LAST);
  EXCEPTION
    WHEN OTHERS THEN
    IF SQLCODE = -1436 THEN
     -- MESSAGE(EM_GLOBAL.ERR_DESC('QUERY_003'),NO_ACKNOWLEDGE);
      SYSMSG.EMMSG(990029);
    ELSE
      --MESSAGE(SQLERRM,NO_ACKNOWLEDGE);
      SYSMSG.EMMSG(SQLERRM,ISELTSTRING=>'Y');
    END IF;
    RAISE FORM_TRIGGER_FAILURE;
END;

#2

START WITH 部分是父条件
CONNECT BY PRIOR  部分是子条件

#3

select lpad(id,level*2+length(id),' ') id
from tabname  connect by prior 编号= 父编号
start with 编号=1000;

详情可参考以下:
http://search.csdn.net/expert/topic/61/6101/2003/3/19/1551178.htm

#4

create table testtree(id int,priorid int,name varchar2(20));
insert into testtree values(1,0,'中国a');
insert into testtree values(2,0,'美国');
insert into testtree values(3,0,'加拿大');
insert into testtree values(4,1,'北京');
insert into testtree values(5,1,'上海');
insert into testtree values(6,1,'江苏');
insert into testtree values(7,6,'苏州');
insert into testtree values(8,7,'常熟');
insert into testtree values(9,6,'南京');
insert into testtree values(10,6,'无锡');
insert into testtree values(11,2,'纽约');
insert into testtree values(12,2,'旧金山');


----全显示-----
select lpad(' ',level-1,' ')||name name from testtree a start with priorid=0 connect by prior id = priorid;

----按子找父---
select lpad(' ',level-1,' ')||name name from testtree a start with id=8 connect by  id=prior priorid;

---按父找子--
select lpad(' ',level-1,' ')||name name from testtree a start with id=1 connect by prior id=priorid;

--统计下级人数--
select lpad(' ',level-1,'')||name name,(select count(*) from testtree connect by prior id=priorid start with id=a.id) num from testtree a connect by prior id=priorid start with id=1;

#5

好详细呀.

#6

支持 beckhambobo(beckham)

#7

beckhambobo(beckham)说的办法最简单、实用，试一试。

#8

beckhambobo(beckham)说的是按父找子吧？yangyiyao(yangyang)不是说要从子找父吗？不过lpad(id,level*2+length(id),' ')写得很棒！