Oracle中使用Start With connect by prior实现树功能

select '|' || lpad(level, level * 4 + 1, '_') || '' ||a. member_name member_cname,
a.member_type,
decode(a.member_type,'加盟店',decode(a.MEMBER_LEVEL,1,'专门店','2','指导级店','3','资深指导级店','4','地区督导','5','全国督导','6','其它')
,'咨询师',decode(a.MEMBER_LEVEL,'0','申请中','1','初阶','2','中阶','3','高阶'))member_level,
a.member_code,
a.ALONE_BV s_bv,
a.TUANDUI_BV t_bv,
a.PROVINCE province_name,
a.city region_name
from (select 
member_name,
MEMBER_TYPE,
max(MEMBER_LEVEL) MEMBER_LEVEL,
MEMBER_CODE,
nvl(sum(ALONE_BV),0)  ALONE_BV,
nvl(sum(TUANDUI_BV),0)  TUANDUI_BV,
PROVINCE,
CITY,
RECOMMEND_CODE
from  ALL_MEMBERS_YJ
where year>=[year_from] and month>=[month_from]
  and year<=[year_to] and month<=[month_to] 
  and is_cancel=decode([s_cancle],'4',is_cancel,'1','1',is_cancel)
 group by member_name,MEMBER_TYPE,MEMBER_CODE,PROVINCE,CITY,RECOMMEND_CODE)a 
 start with a.member_code =[member_code]
 connect by prior a.member_code = a.recommend_code