需求:所属机构显示格式从公司节点开始,到员工所处节点
此处员工所属机构是:电销营业部,即要根据parentCode 递归查询出所在机构及以上机构,一直到公司层次
select replace(wm_concat(name),',','/') fullName
from (select name from ct_t_am_organization start with code = '001001001001003'
connect by prior parent_code = code and parent_code is not null order by parent_code)s ;
要点:
replace(wm_concat(name),',','/')
start with
connect by prior
查询结果:善悉商务/北区/第一分部/电销营业部
pivot(行转列) 和unpivot(列转行)
递归查询信审中心下面的所有正常状态的机构信息(查询结果是一棵树结构,根节点为三个,为信审中下下面的三个直接子节点)
select * from ct_t_am_organization start with parent_code = '001003' CONNECT BY parent_code = PRIOR code and status in('正常'); --001003信审中心的code
此处员工所属机构是:电销营业部,即要根据parentCode 递归查询出所在机构及以上机构,一直到公司层次
select replace(wm_concat(name),',','/') fullName
from (select name from ct_t_am_organization start with code = '001001001001003'
connect by prior parent_code = code and parent_code is not null order by parent_code)s ;
要点:
replace(wm_concat(name),',','/')
start with
connect by prior
查询结果:善悉商务/北区/第一分部/电销营业部
pivot(行转列) 和unpivot(列转行)
递归查询信审中心下面的所有正常状态的机构信息(查询结果是一棵树结构,根节点为三个,为信审中下下面的三个直接子节点)
select * from ct_t_am_organization start with parent_code = '001003' CONNECT BY parent_code = PRIOR code and status in('正常'); --001003信审中心的code