Sql级联查询递归查询start with 。。。connect by prior child=parent

转自http://blog.chinaunix.net/uid-3634-id-2129988.html

分类： Oracle

    最近缴费卡并卡事件较多，为了方便跟踪，使用start with……connect by可以对卡的并卡状况进行一系列的跟踪。例如：

SQL> SELECT LPAD(outcardnumber, LENGTH(outcardnumber)+(LEVEL*2) -2,'_')
  2  AS cardnumber
  3  FROM rcpms.rcpms_unitecard
  4  START WITH outcardnumber='030399529176'
  5  CONNECT BY PRIOR incardnumber=outcardnumber;

CARDNUMBER
--------------------------------------------------------------------------------
030399529176
__030386295685
____030350690996
______030394432312
________030361447641
__________030389355736
____________030352075862
______________030366761588
________________030387064026
__________________030365336999
____________________030334258646
______________________030355463544
________________________030318082105
__________________________030301996949

 

SQL> SELECT LPAD(outcardnumber, LENGTH(outcardnumber)+(LEVEL*2) -2,'_')
  2  AS cardnumber
  3  FROM rcpms.rcpms_unitecard
  4  START WITH outcardnumber='030399529176'
  5  CONNECT BY PRIOR outcardnumber=incardnumber;

CARDNUMBER
--------------------------------------------------------------------------------
030399529176
__030383691552
____030397699599
______030356960881
________030346210764
__________030320028697
____________030339419184
______________030357446358
________________030341150148
__________________030300297116
____________________030308827641

 

这里，根据connect子句中条件的位置不同可以实现向上或向下的分级查询，即第一个例子是以030399529176作为并出卡跟踪一系列的并入卡，第二个例子是以同样的030399529176作为并入卡，找出一系列的并出卡。

 

自顶向下的规则是：

Top down： Column1 = Parent Key
         Column2 = Child Key

自底向上的规则是
Bottom up Column1 = Child Key
          Column2 = Parent Key

例1中可以理解为incardnumber的子行去等于outcardnumber的父行，因此是一种自底向上的行为。例2可以理解为outcardnumber的子行去等于incardnumber的父行，因此是一种自上而下的行为

 

另外，这里lpad函数用来填充字段串左边的位置，从而达到格式化的目的。

 

这里需要注意使用start with……connect by子句还需要注意where和connect by中条件的区别，

例如 where outmoney=100是去除了outmoney不等于的100的树中的所有节点，而

CONNECT BY PRIOR outcardnumber=incardnumber and outmoney=100将去除不等于100的节点下的所有分支。