高分急求SQL语句,谢谢!!!!!!!!

Question

有一表dept,通过top和ref字段形成一个数据结构中的森林状的结构关系,森林中有若干棵树,每个树结点拥有不定个孩子结点
deptid是结点ID,top是树根结点的id,
ref指向双亲结点ID
deepest是结点的最大深度
先说明一下deepest字段.
例如有一棵树:
                      A
                     / \
                    B   C
                   / \
                  D   E
A结点有两个分支,A分支的深度为2,C分支深度为1,所以A结点的最大深度deepest应该为2现需要一个SQL语句,实现以下功能:
给出一棵树中的某几个结点,分别查询这些结点中,使该结点拥有最大深度的分枝个数例如,以下是表中以deptid=s11为顶级部门的部门结构的记录
deptid top ref deepest
s11 s11 NULL 4
s21 s11 s11 0
s22 s11 s11 3
s23 s11 s11 1
s31 s11 s22 2
s32 s11 s23 0
s41 s11 s31 1
s42 s11 s31 1
s43 s11 s31 1
s51 s11 s41 0
s52 s11 s42 0
s53 s11 s43 0
其树结构图为:
                      S11
                    /  |  \
                 S21  S22  S23
                       |     \
                      S31    S32
                    /  |  \
                 S41  S42  S43
                 /     |     \
               S51    S52     S53现在给出S41、S31、S22、S11这几个结点，查询每个结点使其拥有最大深度的分支个数
按这课树的结构，再解释下我要得到的是什么：
例如，S23的deepest为1，使S23拥有最大深度的分支只有S32，所以S23的brachcount为1
S31的deepest为2,使S31拥有最大深度的分支有S41，S42，S43三个，所以S31的branchcount为3
所以
我要的预期结果是：
deptid branchcount
S41 1
S31 2
S22 1
S11 1
请问这样的SQL语句应该怎样写呢？希望尽量是一条SQL语句，且查询的效率越高越好。
急啊，欢迎各位高手拔刀相助，非常感谢！！

Answer 1

select 's41',count(aa.*) branchcount
from
(
select *
from a
 CONNECT BY PRIOR deptid= ref
START WITH deptid='s41'
) aa
where aa.depid<>'s41'
union all
select 's31',count(aa.*) branchcount
from
(
select *
from a
 CONNECT BY PRIOR deptid= ref
START WITH deptid='s31'
) aa
where aa.depid<>'s31'
union all
select 's22',count(aa.*) branchcount
from
(
select *
from a
 CONNECT BY PRIOR deptid= ref
START WITH deptid='s22'
) aa
where aa.depid<>'s22'
union all
select 's11',count(aa.*) branchcount
from
(
select *
from a
 CONNECT BY PRIOR deptid= ref
START WITH deptid='s11'
) aa
where aa.depid<>'s11'

Answer 2

select 's41' deptid,count(aa.*) branchcount
from
(
select *
from a
 CONNECT BY PRIOR deptid= ref
START WITH deptid='s41'
) aa
where aa.depid<>'s41'
union all
select 's31',count(aa.*) branchcount
from
(
select *
from a
 CONNECT BY PRIOR deptid= ref
START WITH deptid='s31'
) aa
where aa.depid<>'s31'
union all
select 's22',count(aa.*) branchcount
from
(
select *
from a
 CONNECT BY PRIOR deptid= ref
START WITH deptid='s22'
) aa
where aa.depid<>'s22'
union all
select 's11',count(aa.*) branchcount
from
(
select *
from a
 CONNECT BY PRIOR deptid= ref
START WITH deptid='s11'
) aa
where aa.depid<>'s11'

Answer 3

SELECT (
   SELECT COUNT(*) FROM DEPT B 
          WHERE B.REF=A.DEPTID AND (
                 SELECT MAX(LEVEL)+1 FROM DEPT C CONNECT BY PRIOR DEPTID=REF START WITH B.DEPTID
                 ) = A.DEEPEST
) FROM DEPT A
WHERE DEPTID IN('s11','s22','s31','s41');我这里没有环境进行测试，楼主可以自己测试一下看看，基本思路就是这样。 

Answer 4

强  MARK UP  

Answer 5

实际测试了一下，通过。SQL> SELECT * FROM DEPT;DEPTID     TOP        REF          DEEPEST
---------- ---------- ---------- ---------
s11        s11                           4
s21        s11        s11                0
s22        s11        s11                3
s23        s11        s11                1
s31        s11        s22                2
s32        s11        s23                0
s41        s11        s31                1
s42        s11        s31                1
s43        s11        s31                1
s51        s11        s41                0
s52        s11        s42                0
s53        s11        s43                0已选择12行。SQL> SELECT A.DEPTID,(
  2    SELECT COUNT(*) FROM DEPT B 
  3    WHERE B.REF=A.DEPTID 
  4    AND (
  5     SELECT MAX(LEVEL) FROM DEPT C CONNECT BY PRIOR DEPTID=REF START WITH DEPTID=B.DEP
  6        )=A.DEEPEST
  7    ) AS BRANCHCOUNT 
  8  FROM DEPT A
  9  WHERE DEPTID IN('s11','s22','s31','s41');DEPTID     BRANCHCOUNT
---------- -----------
s11                  1
s22                  1
s31                  3
s41                  1SQL>