树状结构如下
广东省202913684830
--------越秀区202913684831
-------------麓景麓202913684833
-------------越秀路202913684837
--------天河区202913684832
-------------天河路202913684838
第一种方式
select a.treegroup_id,a.treegroup_parent,a.treegroup_name,
(select count(*)
from j_treedepot b
where b.treegroup_parent=a.treegroup_id) sub_cnt
from j_treedepot a
where a.treegroup_parent=202913684830
order by 1;
第二种方式
with parent as (
select a.treegroup_parent, count(*) sub_cnt
from j_treedepot a
group by a.treegroup_parent
order by 1
)
select a.treegroup_id, a.treegroup_name, nvl(b.sub_cnt,0) sub_cnt
from j_treedepot a
left join parent b on a.treegroup_id=b.treegroup_parent
where a.treegroup_parent=202913684830
order by 1
表结构如下
