树表查询问题

Question

树表
userid   parentid   others
A         
B         A           tre
C         A           weo  
D         B           rew  
E         B           wer
F         B           yht
G         C           rew  
H         C           UIO
I         D           YYY 
J         C           MMM
K         C           UUU 如果我要得到第二级（B，C）的如下资料
userid   parentid  others  一级子帐号个数  二级子帐号个数
 B         A        tre      3               1
 C         A        weo      2               2请问该如何实现？谢谢！

Answer 1

/* Formatted on 2008/06/12 22:24 (Formatter Plus v4.8.8) */
WITH a AS
     (SELECT 'A' userid, NULL parentid, NULL OTHERS
        FROM DUAL
      UNION ALL
      SELECT 'B', 'A', 'tre'
        FROM DUAL
      UNION ALL
      SELECT 'C', 'A', 'weo'
        FROM DUAL
      UNION ALL
      SELECT 'D', 'B', 'rew'
        FROM DUAL
      UNION ALL
      SELECT 'E', 'B', 'wer'
        FROM DUAL
      UNION ALL
      SELECT 'F', 'B', 'yht'
        FROM DUAL
      UNION ALL
      SELECT 'G', 'C', 'rew'
        FROM DUAL
      UNION ALL
      SELECT 'H', 'C', 'UIO'
        FROM DUAL
      UNION ALL
      SELECT 'I', 'D', 'YYY'
        FROM DUAL
      UNION ALL
      SELECT 'J', 'G', 'MMM'
        FROM DUAL
      UNION ALL
      SELECT 'K', 'H', 'UUU'
        FROM DUAL)SELECT aa.userid, aa.parentid, aa.OTHERS, aa.one_level "一级子帐号个数",
       aa.two_level "二级子帐号个数"
  
FROM   (SELECT     a.*, SUBSTR (SYS_CONNECT_BY_PATH (userid, ','), 2, 1) tree,
                   LEVEL lv,
                   SUM (DECODE (LEVEL, 2, 1, 0)) OVER (PARTITION BY SUBSTR
                                                                      (SYS_CONNECT_BY_PATH
                                                                          (userid,
                                                                           ','
                                                                          ),
                                                                       2,
                                                                       1
                                                                      ))
                                                                    one_level,
                   SUM (DECODE (LEVEL, 3, 1, 0)) OVER (PARTITION BY SUBSTR
                                                                      (SYS_CONNECT_BY_PATH
                                                                          (userid,
                                                                           ','
                                                                          ),
                                                                       2,
                                                                       1
                                                                      ))
                                                                    two_level
              FROM a
        START WITH a.parentid = 'A'
        CONNECT BY PRIOR a.userid = a.parentid) aa
WHERE aa.lv = 1结果
Row# USERID PARENTID OTHERS 一级子帐号个数 二级子帐号个数1 B A tre 3 1
2 C A weo 2 2