3,491
社区成员
发帖
与我相关
我的任务
分享
with table1 as (
select 'A' as tuijian_no, 'B' as no from dual union all
select 'A' as tuijian_no, 'C' as no from dual union all
select 'B' as tuijian_no, 'D' as no from dual union all
select 'C' as tuijian_no, 'E' as no from dual union all
select 'A' as tuijian_no, 'F' as no from dual union all
select 'C' as tuijian_no, 'G' as no from dual union all
select 'G' as tuijian_no, 'H' as no from dual union all
select 'B' as tuijian_no, 'I' as no from dual union all
select 'A' as tuijian_no, 'J' as no from dual
),
table2 as (
select 'A' AS NO,3 AS no_level from dual union all
select 'B' AS NO,0 AS no_level from dual union all
select 'C' AS NO,2 AS no_level from dual union all
select 'D' AS NO,1 AS no_level from dual union all
select 'E' AS NO,0 AS no_level from dual union all
select 'F' AS NO,1 AS no_level from dual union all
select 'G' AS NO,2 AS no_level from dual union all
select 'H' AS NO,0 AS no_level from dual union all
select 'I' AS NO,3 AS no_level from dual union all
select 'J' AS NO,0 AS no_level from dual
)
select * from table1 a , table2 b
where a.no = b.no
with table1 as (
select 'A' as tuijian_no, 'B' as no, 1 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'C' as no, 2 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'D' as no, 1 AS tuijian_line, 2 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'E' as no, 2 AS tuijian_line, 2 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'F' as no, 1 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'G' as no, 2 AS tuijian_line, 2 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'H' as no, 2 AS tuijian_line, 3 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'I' as no, 1 AS tuijian_line, 2 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'J' as no, 1 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'B' as tuijian_no, 'D' as no, 1 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'B' as tuijian_no, 'I' as no, 2 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'C' as tuijian_no, 'E' as no, 1 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'C' as tuijian_no, 'G' as no, 2 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'C' as tuijian_no, 'H' as no, 2 AS tuijian_line, 2 as tuijian_lv from dual union all
select 'G' as tuijian_no, 'H' as no, 1 AS tuijian_line, 1 as tuijian_lv from dual
),
table2 as (
select 'A' AS NO,3 AS no_level from dual union all
select 'B' AS NO,0 AS no_level from dual union all
select 'C' AS NO,2 AS no_level from dual union all
select 'D' AS NO,1 AS no_level from dual union all
select 'E' AS NO,1 AS no_level from dual union all
select 'F' AS NO,1 AS no_level from dual union all
select 'G' AS NO,2 AS no_level from dual union all
select 'H' AS NO,0 AS no_level from dual union all
select 'I' AS NO,3 AS no_level from dual union all
select 'J' AS NO,0 AS no_level from dual
)
with table1 as (
select 'A' as tuijian_no, 'B' as no, 1 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'C' as no, 2 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'D' as no, 1 AS tuijian_line, 2 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'E' as no, 2 AS tuijian_line, 2 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'F' as no, 1 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'G' as no, 2 AS tuijian_line, 2 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'H' as no, 2 AS tuijian_line, 3 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'I' as no, 1 AS tuijian_line, 2 as tuijian_lv from dual union all
select 'A' as tuijian_no, 'J' as no, 1 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'B' as tuijian_no, 'D' as no, 1 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'B' as tuijian_no, 'I' as no, 2 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'C' as tuijian_no, 'E' as no, 1 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'C' as tuijian_no, 'G' as no, 2 AS tuijian_line, 1 as tuijian_lv from dual union all
select 'C' as tuijian_no, 'H' as no, 2 AS tuijian_line, 2 as tuijian_lv from dual union all
select 'G' as tuijian_no, 'H' as no, 1 AS tuijian_line, 1 as tuijian_lv from dual
),
table2 as (
select 'A' AS NO,3 AS no_level from dual union all
select 'B' AS NO,3 AS no_level from dual union all
select 'C' AS NO,1 AS no_level from dual union all
select 'D' AS NO,1 AS no_level from dual union all
select 'E' AS NO,1 AS no_level from dual union all
select 'F' AS NO,1 AS no_level from dual union all
select 'G' AS NO,0 AS no_level from dual union all
select 'H' AS NO,1 AS no_level from dual union all
select 'I' AS NO,3 AS no_level from dual union all
select 'J' AS NO,0 AS no_level from dual
)
with table1 as (
select 'A' as tuijian_no, 'B' as no from dual union all
select 'A' as tuijian_no, 'C' as no from dual union all
select 'B' as tuijian_no, 'D' as no from dual union all
select 'C' as tuijian_no, 'E' as no from dual union all
select 'A' as tuijian_no, 'F' as no from dual union all
select 'C' as tuijian_no, 'G' as no from dual union all
select 'G' as tuijian_no, 'H' as no from dual union all
select 'B' as tuijian_no, 'I' as no from dual union all
select 'A' as tuijian_no, 'J' as no from dual
),
table2 as (
select 'A' AS NO,3 AS no_level from dual union all
select 'B' AS NO,0 AS no_level from dual union all
select 'C' AS NO,2 AS no_level from dual union all
select 'D' AS NO,1 AS no_level from dual union all
select 'E' AS NO,1 AS no_level from dual union all
select 'F' AS NO,1 AS no_level from dual union all
select 'G' AS NO,2 AS no_level from dual union all
select 'H' AS NO,0 AS no_level from dual union all
select 'I' AS NO,3 AS no_level from dual union all
select 'J' AS NO,0 AS no_level from dual
)
,table3 as(select t.*,s.no_level from (
select a.*,connect_by_root(no) n1,level n2 from table1 a start with tuijian_no ='A' connect by nocycle tuijian_no = prior no ) t
,(select * from table2 where no_level >0) s where t.no = s.no
)
--select *from table3
select
TUIJIAN_NO,
NO,
NO_LEVEL
from (
select t1.* ,row_number() over (partition by case when (select no_level from table2 where no =t1.n1) =0 then no else n1 end order by n2 ) rn from table3 t1) where rn =1
改了一下,with table1 as (
select 'A' as tuijian_no, 'B' as no from dual union all
select 'A' as tuijian_no, 'C' as no from dual union all
select 'B' as tuijian_no, 'D' as no from dual union all
select 'C' as tuijian_no, 'E' as no from dual union all
select 'A' as tuijian_no, 'F' as no from dual union all
select 'C' as tuijian_no, 'G' as no from dual union all
select 'G' as tuijian_no, 'H' as no from dual union all
select 'B' as tuijian_no, 'I' as no from dual union all
select 'A' as tuijian_no, 'J' as no from dual
),
table2 as (
select 'A' AS NO,3 AS no_level from dual union all
select 'B' AS NO,0 AS no_level from dual union all
select 'C' AS NO,2 AS no_level from dual union all
select 'D' AS NO,1 AS no_level from dual union all
select 'E' AS NO,1 AS no_level from dual union all
select 'F' AS NO,1 AS no_level from dual union all
select 'G' AS NO,2 AS no_level from dual union all
select 'H' AS NO,0 AS no_level from dual union all
select 'I' AS NO,3 AS no_level from dual union all
select 'J' AS NO,0 AS no_level from dual
)
,table3 as(select t.*,s.no_level from (
select a.*,connect_by_root(no) n1,level n2 from table1 a start with tuijian_no ='A' connect by nocycle tuijian_no = prior no ) t
,(select * from table2 where no_level >0) s where t.no = s.no
) --select * from table3
select TUIJIAN_NO,
NO,
NO_LEVEL
from (
select t1.* ,row_number() over (partition by n1,no_level order by n2 ) rn from table3 t1) where rn =1
with table1 as (
select 'A' as tuijian_no, 'B' as no from dual union all
select 'A' as tuijian_no, 'C' as no from dual union all
select 'B' as tuijian_no, 'D' as no from dual union all
select 'C' as tuijian_no, 'E' as no from dual union all
select 'A' as tuijian_no, 'F' as no from dual union all
select 'C' as tuijian_no, 'G' as no from dual union all
select 'G' as tuijian_no, 'H' as no from dual union all
select 'B' as tuijian_no, 'I' as no from dual union all
select 'A' as tuijian_no, 'J' as no from dual
),
table2 as (
select 'A' AS NO,3 AS no_level from dual union all
select 'B' AS NO,0 AS no_level from dual union all
select 'C' AS NO,2 AS no_level from dual union all
select 'D' AS NO,1 AS no_level from dual union all
select 'E' AS NO,0 AS no_level from dual union all
select 'F' AS NO,1 AS no_level from dual union all
select 'G' AS NO,2 AS no_level from dual union all
select 'H' AS NO,0 AS no_level from dual union all
select 'I' AS NO,3 AS no_level from dual union all
select 'J' AS NO,0 AS no_level from dual
)
,table3 as(select t.*,s.no_level from (
select a.*,connect_by_root(no) n1,level n2 from table1 a where level in (1,2) start with tuijian_no ='A' connect by nocycle tuijian_no = prior no ) t
,(select * from table2 where no_level >0) s where t.no = s.no
)
--select *from table3
select TUIJIAN_NO,
NO,
NO_LEVEL
from (
select t1.* ,row_number() over (partition by n1,no_level order by n2 ) rn from table3 t1) where rn =1
结果是这样的了,我指定的是A节点,其他节点没试过,你自己造数据试一下with table1 as (
select 'A' as tuijian_no, 'B' as no from dual union all
select 'A' as tuijian_no, 'C' as no from dual union all
select 'B' as tuijian_no, 'D' as no from dual union all
select 'C' as tuijian_no, 'E' as no from dual union all
select 'A' as tuijian_no, 'F' as no from dual union all
select 'C' as tuijian_no, 'G' as no from dual union all
select 'G' as tuijian_no, 'H' as no from dual union all
select 'B' as tuijian_no, 'I' as no from dual union all
select 'A' as tuijian_no, 'J' as no from dual
),
table2 as (
select 'A' AS NO,3 AS no_level from dual union all
select 'B' AS NO,0 AS no_level from dual union all
select 'C' AS NO,2 AS no_level from dual union all
select 'D' AS NO,1 AS no_level from dual union all
select 'E' AS NO,0 AS no_level from dual union all
select 'F' AS NO,1 AS no_level from dual union all
select 'G' AS NO,2 AS no_level from dual union all
select 'H' AS NO,0 AS no_level from dual union all
select 'I' AS NO,3 AS no_level from dual union all
select 'J' AS NO,0 AS no_level from dual
),
table3 as (
select b.root,b.no,b.lv,d.no_level from
(
select CONNECT_BY_ROOT(a.tuijian_no) root, a.no, level lv
from table1 a
connect by prior a.no = a.tuijian_no
) b,table2 d
where b.no=d.no and b.no in (select c.no from table2 c where c.no=b.no and c.no_level>0)
order by root
)
select a.root,a.no,a.no_level
from table3 a
where not exists(select 1 from table3 b where a.no=b.root)
order by root