为什么结果是这样？和预期有点不一样

Question

脚本：
/* Formatted on 2008/11/15 11:10 (Formatter Plus v4.8.6) */
SELECT LPAD(' ', 5*(LEVEL - 1))||son son, LPAD(' ', 5*(LEVEL - 1))||dad dad 
      FROM qwx.visit_tree
START WITH son = 'A'
CONNECT BY dad = PRIOR son;运行结果：
SON                                                                             
--------------------------------------------------------------------------------
DAD                                                                             
--------------------------------------------------------------------------------
A                                                                               
root                                                                            
                                                                                
     B                                                                          
     A                                                                          
                                                                                
          E                                                                     
          B                                                                     
                                                                                
          F                                                                     
          B                                                                     
                                                                                
               H                                                                
               F                                                                
                                                                                
               I                                                                
               F                                                                
                                                                                
     C                                                                          SON                                                                             
--------------------------------------------------------------------------------
DAD                                                                             
--------------------------------------------------------------------------------
     A                                                                          
                                                                                
          G                                                                     
          C                                                                     
                                                                                
               J                                                                
               G                                                                
                                                                                
               K                                                                
               G                                                                
                                                                                
               L                                                                
               G                                                                
                                                                                
     D                                                                          
     A                                                                          
                                                                                12 rows selected.

Answer 1

--在网友原文(http://topic.csdn.net/t/20020711/11/866042.html)的基础上作了些修改，参考一下：SQL> drop table test_tree;Table droppedSQL> create  table   test_tree
  2    (
  3        userid number(8) not null primary key,
  4        username varchar2(18) not null,
  5        p_username varchar2(18),
  6        num number(10,2)
  7    );Table createdSQL>   insert   into   test_tree   values(1,'1000',null,null);1 row insertedSQL>   insert   into   test_tree   values(2,'1100','1000',null);1 row insertedSQL>   insert   into   test_tree   values(3,'1200','1000',null);1 row insertedSQL>   insert   into   test_tree   values(4,'1101','1100',100);1 row insertedSQL>   insert   into   test_tree   values(5,'1102','1100',200);1 row insertedSQL>   insert   into   test_tree   values(6,'1104','1100',250);1 row insertedSQL>   insert   into   test_tree   values(7,'1201','1200',150);1 row insertedSQL>   insert   into   test_tree   values(8,'1205','1200',200);1 row insertedSQL>   insert   into   test_tree   values(9,'1207','1200',150);1 row insertedSQL>   commit;Commit completeSQL> select * from test_tree;   USERID USERNAME           P_USERNAME                  NUM
--------- ------------------ ------------------ ------------
        1 1000                                  
        2 1100               1000               
        3 1200               1000               
        4 1101               1100                     100.00
        5 1102               1100                     200.00
        6 1104               1100                     250.00
        7 1201               1200                     150.00
        8 1205               1200                     200.00
        9 1207               1200                     150.009 rows selectedSQL> select b.username,
  2         b.p_username,
  3         (select distinct sum(nvl(a.num, 0))
  4            from test_tree a
  5           start with a.username = b.username
  6          connect by a.p_username = prior a.username) sum
  7    from test_tree b
  8   start with username = '1000'
  9  connect by p_username = prior username;USERNAME           P_USERNAME                SUM
------------------ ------------------ ----------
1000                                        1050
1100               1000                      550
1101               1100                      100
1102               1100                      200
1104               1100                      250
1200               1000                      500
1201               1200                      150
1205               1200                      200
1207               1200                      1509 rows selectedSQL> 

Answer 2

我的预期结果是这样
SON                       DAD                                                            -------------------------  ------------------------
A                         root                                   
                                                                                
    B                            A                           
                                                                                
          E                            B                      
                                                                                
          F                            B                                 
                                                                                
              H                              F
              I                              F
    C                            A
         G                             C
              J                              G
              K                              G
              L                              G
    D                            A