Oracle connect by 示例

SQL> create TABLE tb(ID   char(3),PID   char(3),Name  varchar(10)) ;


Table created.


SQL> INSERT  into tb SELECT   '001',NULL   ,'山东省'  from dual
  2    UNION   ALL   SELECT   '002','001','烟台市'     from dual
  3    UNION   ALL   SELECT   '004','002','招远市'     from dual
  4    UNION   ALL   SELECT   '003','001','青岛市'     from dual
  5    UNION   ALL   SELECT   '005',NULL   ,'四会市'     from dual
  6    UNION   ALL   SELECT   '006','005','清远市'     from dual
  7    UNION   ALL   SELECT   '007','006','小分市'   from dual   ;


7 rows created.




SQL> select id, pid, lpad(' ', level*2, ' ') || name newname from tb start with pid is null connect
by prior id = pid;


ID  PID
--- ---
NEWNAME
--------------------------------------------------------------------------------
001
  山东省


002 001
    烟台市


004 002
      招远市




ID  PID
--- ---
NEWNAME
--------------------------------------------------------------------------------
003 001
    青岛市


005
  四会市


006 005
    清远市




ID  PID
--- ---
NEWNAME
--------------------------------------------------------------------------------
007 006
      小分市




7 rows selected.


SQL>