oracle 树形查询，递归sql 的写法！！！！

表和数据：

with table1 as (

     select 'A' as tuijian_no, 'B' as no from dual union all

     select 'A' as tuijian_no, 'C' as no from dual union all

     select 'B' as tuijian_no, 'D' as no from dual union all

     select 'C' as tuijian_no, 'E' as no from dual union all

     select 'A' as tuijian_no, 'F' as no from dual union all

     select 'C' as tuijian_no, 'G' as no from dual union all

     select 'G' as tuijian_no, 'H' as no from dual union all

     select 'B' as tuijian_no, 'I' as no from dual union all

     select 'A' as tuijian_no, 'J' as no from dual

),

table2 as (

       select 'A' AS NO,3 AS no_level from dual union all

       select 'B' AS NO,0 AS no_level from dual union all

       select 'C' AS NO,2 AS no_level from dual union all

       select 'D' AS NO,1 AS no_level from dual union all

       select 'E' AS NO,0 AS no_level from dual union all

       select 'F' AS NO,1 AS no_level from dual union all

       select 'G' AS NO,2 AS no_level from dual union all

       select 'H' AS NO,0 AS no_level from dual union all

       select 'I' AS NO,3 AS no_level from dual union all

       select 'J' AS NO,0 AS no_level from dual

)

select * from table1 a , table2 b

where a.no = b.no

用户树形关系图和等级：


table1 是用户的树形关系图，table2是用户的等级信息。

现在需要查询出用户的下级用户等级的个数，但需要注意的是一条线只找到第一个用户的等级就够了。

比如，A用户，虽然只有BCFJ，这2个节点，但B和C都还有节点，所以A用户在查找下级的等级情况结果如下：
下级用户 等级
D 1
I 3
C 2
F 1

G用户的等级不能查出来，因为G用户那条线上的C已经被统计过了。所以不能再继续统计了。

求思路写法。！！分数不多了。感谢