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