Oracle ~ 之创建树状结构

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