X2-3.0 Oracle 获取店铺顶级公司SQL脚本

with parent as (

select connect_by_root department_id as depot_id,

 a.department_id as company_id, 

 a.department_parent_id, level

from d0060 a 

start with  a.system_type  in (10,11) 

connect by prior a.department_parent_id = a.department_id

order by level

)

 

select a.depot_id, b.department_user_id as depot_code, b.department_name as depot_name, 

   a.company_id, c.department_user_id as company_code, c.department_name as company_name

from parent a inner join v0060 b on a.depot_id=b.department_id and b.language_id='zh-cn'

          inner join v0060 c on a.company_id=c.department_id and c.language_id='zh-cn'

where a.department_parent_id=0