--树显示子结点

 

--树显示子结点
   select *
     from (select b.*,
                  row_number() over(partition by rownum - level order by level desc) rn,
                  level,
                  rownum,
                  rownum - level
             from BREEDING b
            start with Offspring = 'EVE'
           connect by prior offspring = Cow)
  --  where rn = 1
  
  /*
  从根结点开始的遍历(rownum-level相等的是一个分支)
  */
  
  select b.*,
                 
                  level,
                  rownum,
                  rownum - level
             from BREEDING b
            start with Offspring = 'EVE'
           connect by prior offspring = Cow



       OFFSPRING    SEX    COW    BULL    BIRTHDATE    LEVEL    ROWNUM    ROWNUM-LEVEL
1    EVE    F                        1    1    0
2    BETSY    F    EVE    ADAM    2000-1-2    2    2    0
3    NOVI    F    BETSY    ADAM    2003-3-30    3    3    0
4    GINNY    F    BETSY    BANDIT    2003-12-4    3    4    1
5    SUZY    F    GINNY    DUKE    2006-4-3    4    5    1
6    DELLA    F    SUZY    BANDIT    2008-10-11    5    6    1
7    RUTH    F    GINNY    DUKE    2006-12-26    4    7    3
8    TEDDI    F    BETSY    BANDIT    2005-8-12    3    8    5
9    POCO    M    EVE    ADAM    2000-7-15    2    9    7
10    GRETA    F    EVE    BANDIT    2001-3-12    2    10    8
11    MANDY    F    EVE    POCO    2002-8-22    2    11    9
12    DUKE    M    MANDY    BANDIT    2004-7-24    3    12    9
13    PAULA    F    MANDY    POCO    2006-12-21    3    13    10
14    CINDY    F    EVE    POCO    2003-2-9    2    14    12