with s as
(select XZJGDM,SJXZJGDM,level lel
from t
connect by prior XZJGDM = SJXZJGDM
start with XZJGDM = '433127')
select a.SJXZJGDM,a.num_ctr "该乡镇村数",b.num_grp "该乡镇组数"
from
(select SJXZJGDM,count(*) num_ctr
from s
where lel = 3
group by SJXZJGDM
) a,
(select substr(SJXZJGDM,1,8) dwdm,count(*) num_grp
from s
where lel = 4
group by substr(SJXZJGDM,1,8)
) b
where a.SJXZJGDM = b.dwdm;
|