1
select
t.branch_id,
t.branch_no,
t.branch_name,
t.parent_branch_id,
t.branch_id as key
from (
--------------------------------------------------------------------------------------------------------------------------------
select
b.branch_id,
b.branch_no,
b.branch_name,
b.parent_branch_id
from aiskbus.branch_dim b
where b.branch_level = 2
and b.branch_no < 1000000
and b.branch_id in
(
select
distinct
branch_id
from aiskbus.branch_dim
start with branch_id in
(
select
branch_id
from aiskbus.branch_dim
where branch_id in
(
select
t3.branch_id
from aiskbus.branch_dim t3
left join aiskbus.branch_dim t4
on t3.branch_id = t4.branch_id
where 1 = 1
-- <if test="_parameter!=null and _parameter!=''">
-- and instr(t3.branch_name,#{_parameter})<![CDATA[>]]>0
-- </if>
)
)
connect by branch_id = prior parent_branch_id
)
--------------------------------------------------------------------------------------------------------------------------------
union all
select
b.branch_id,
b.branch_no,
b.branch_name,
b.parent_branch_id
from aiskbus.branch_dim b
where b.branch_level = 3
and b.branch_no < 1000000
and b.source_system_id = '1'
and b.branch_id in
(
select
distinct
branch_id
from aiskbus.branch_dim
start with branch_id in
(
select
branch_id
from aiskbus.branch_dim
where branch_id in
(
select
t3.branch_id
from aiskbus.branch_dim t3
left join aiskbus.branch_dim t4
on t3.branch_id = t4.branch_id
where 1 = 1
-- <if test="_parameter!=null and _parameter!=''">
-- and instr(t3.branch_name,#{_parameter})<![CDATA[>]]>0
-- </if>
)
)
connect by prior branch_id = parent_branch_id
)
--------------------------------------------------------------------------------------------------------------------------------
union all
select
b.branch_id,
b.branch_no,
b.branch_name,
b.parent_branch_id
from aiskbus.branch_dim b
where b.branch_level = 1
and b.branch_no = 8888
) t
start with parent_branch_id = '0'
connect by prior branch_id = parent_branch_id
2
select
t.branch_id,
t.branch_no,
t.branch_name,
t.parent_branch_id,
t.branch_id as key
from (
--------------------------------------------------------------------------------------------------------------------------------
select --拿到二级菜单,营业部
b.PTY_ID as branch_id,
b.PTY_ID as branch_no,
b.ORG_DESC as branch_name,
b.UP_PTY_ID as parent_branch_id
from AISKBUS.sta_pty_org_base b
where (org_type_name like '辖属营业部' or org_type_name like '分公司部门') or ( org_type_name is null and org_desc not like '%(已撤销)'
and UP_PTY_ID in (
select
distinct
b.PTY_ID
from AISKBUS.sta_pty_org_base b
where b.UP_PTY_ID like 'ZZ001041'
and b.ORG_DESC like '%分公司'
)
--where b.branch_level = 2
--and b.branch_no < 1000000
and b.PTY_ID in
(
select
distinct
PTY_ID
from AISKBUS.sta_pty_org_base
start with PTY_ID in
(
select
PTY_ID
from AISKBUS.sta_pty_org_base
where PTY_ID in
(
select
t3.PTY_ID
from AISKBUS.sta_pty_org_base t3
left join AISKBUS.sta_pty_org_base t4
on t3.PTY_ID = t4.PTY_ID
)
)
connect by PTY_ID = prior UP_PTY_ID
)
--------------------------------------------------------------------------------------------------------------------------------
union all --拿到一级菜单,公司名称
select
b.PTY_ID as branch_id,
b.PTY_ID as branch_no,
b.ORG_DESC as branch_name,
b.UP_PTY_ID as parent_branch_id
from AISKBUS.sta_pty_org_base b
where b.UP_PTY_ID like 'ZZ001041'
and b.ORG_DESC like '%分公司'
) t
start with parent_branch_id = 'ZZ001041'
connect by prior branch_id = parent_branch_id