递归查询

CAP_CAPITEM表中的数据如下：
CAPITEM_CODE
1
101
10101
10102
10103
102
10201
103
104
10401
105
106
10601
108
10801

给出子代码获得所有父代码(包括自己)
select
a.CAPITEM_CODE
          from
         CAP_CAPITEM a,
         CAP_CAPITEM b      
              where substr(b.CAPITEM_CODE,1,length(a.CAPITEM_CODE))=a.CAPITEM_CODE
     and b.CAPITEM_CODE='10103'
     group by a.CAPITEM_CODE
     order by a.CAPITEM_CODE;
执行结果：     
1
101
10103

给出父代码获得所有子代码(包括自己)
select
a.CAPITEM_CODE
          from
         CAP_CAPITEM a,
         CAP_CAPITEM b      
              where substr(a.CAPITEM_CODE,1,length(b.CAPITEM_CODE))=b.CAPITEM_CODE
     and b.CAPITEM_CODE='1'
     group by a.CAPITEM_CODE
     order by a.CAPITEM_CODE;  
执行结果：
1
101
10101
10102
10103
102
10201
103
104
10401
105
106
10601
108
10801     

另外一种写法：

//登陆帐套及其下级(包括下级的下级)的ACCSET_ID,DATASETVER_ID
WITH temptab(ACCSET_ID,DATASETVER_ID) AS
            (    SELECT A.ACCSET_ID,A.DATASETVER_ID
            FROM SYS_DATASETVER A
            WHERE ACCSET_ID=34 and DATASETVER_ID=35
           
            UNION ALL
            SELECT B.ACCSET_ID,B.DATASETVER_ID
            FROM SYS_DATASETVER B, temptab super
            WHERE B.DATASETVER_PARE_ACCSET_ID = super.ACCSET_ID and B.DATASETVER_PARE_ID=super.DATASETVER_ID
            )
 SELECT ACCSET_ID,DATASETVER_ID FROM temptab