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
Oracle中使用Start With connect by prior实现树功能
最新推荐文章于 2021-11-16 23:43:33 发布