oracle 树形查询，递归sql 的写法！！！！

ksdy138141314 2016-07-11 03:42:59
表和数据：
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


用户树形关系图和等级：


table1 是用户的树形关系图，table2是用户的等级信息。

现在需要查询出用户的下级用户等级的个数，但需要注意的是一条线只找到第一个用户的等级就够了。

比如，A用户，虽然只有BCFJ，这2个节点，但B和C都还有节点，所以A用户在查找下级的等级情况结果如下：
下级用户 等级
D 1
I 3
C 2
F 1

G用户的等级不能查出来，因为G用户那条线上的C已经被统计过了。所以不能再继续统计了。

求思路写法。！！分数不多了。感谢
...全文
482 15 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
ksdy138141314 2016-07-12
  • 打赏
  • 举报
 回复
引用 13 楼 wang123kui 的回复:
你能把图画出来么，把正确的结果 写出来，没时间做这些事
图形就和我发的那个图是一样的。只不过现在table1这个表，和原来的关系表有点区别，原来的关系表只记录了推荐人和被推荐人的关系， 现在这个关系表，记录的是每个用户之间的树形关系。比如虽然D用户是B用户推荐的，但由于B用户是A用户推荐的，所以B用户其实对于A用户来讲，也是放在A用户下面的。在A用户的第二层，和B用户在同一条推荐线上。 现在A用户要的结果：--这里和你原来给的sql少了一个D用户的信息，因为D用户和I用户 对于A用户来讲，都是属于同一条线，所以只取最大的一个等级。 C 2 F 1 I 3 用这个表和数据： 

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
)
桃花岛黄岛主 2016-07-12
  • 打赏
  • 举报
 回复
你能把图画出来么，把正确的结果 写出来，没时间做这些事
ksdy138141314 2016-07-12
  • 打赏
  • 举报
 回复
引用 11 楼 wang123kui 的回复:

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, 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
)
桃花岛黄岛主 2016-07-12
  • 打赏
  • 举报
 回复 

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
改了一下，
桃花岛黄岛主 2016-07-12
  • 打赏
  • 举报
 回复
那你把深度代进去，如是一个节也多个深度，按最大的那个深度计算
ksdy138141314 2016-07-11
  • 打赏
  • 举报
 回复
引用 8 楼 wang123kui 的回复:
[quote=引用 7 楼 ksdy138141314 的回复:] [quote=引用 6 楼 wang123kui 的回复:] 
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节点，其他节点没试过，你自己造数据试一下
好的，感谢，这里的 where level in (1,2) 是什么意思呀？？ 为什么要这个条件？[/quote] 那个可以去掉，我加上去是为了去掉没用的叶子节点[/quote] 哥们，我测试了一下，这个有点问题，应该出在partition by那里，根据no_level分区了。如果E用户也有等级，且等级和C不一样，那E用户也都查询出来了。其实不应该出来的： 
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
ksdy138141314 2016-07-11
  • 打赏
  • 举报
 回复
引用 8 楼 wang123kui 的回复:
[quote=引用 7 楼 ksdy138141314 的回复:] [quote=引用 6 楼 wang123kui 的回复:] 
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节点，其他节点没试过，你自己造数据试一下
好的，感谢，这里的 where level in (1,2) 是什么意思呀？？ 为什么要这个条件？[/quote] 那个可以去掉，我加上去是为了去掉没用的叶子节点[/quote] 好的，感谢哥们，我试试
桃花岛黄岛主 2016-07-11
  • 打赏
  • 举报
 回复
引用 7 楼 ksdy138141314 的回复:
[quote=引用 6 楼 wang123kui 的回复:] 
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节点，其他节点没试过，你自己造数据试一下
好的，感谢，这里的 where level in (1,2) 是什么意思呀？？ 为什么要这个条件？[/quote] 那个可以去掉，我加上去是为了去掉没用的叶子节点
ksdy138141314 2016-07-11
  • 打赏
  • 举报
 回复
引用 6 楼 wang123kui 的回复:
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节点，其他节点没试过，你自己造数据试一下
好的，感谢，这里的 where level in (1,2) 是什么意思呀？？ 为什么要这个条件？
桃花岛黄岛主 2016-07-11
  • 打赏
  • 举报
 回复 
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节点，其他节点没试过，你自己造数据试一下
ksdy138141314 2016-07-11
  • 打赏
  • 举报
 回复
引用 4 楼 wang123kui 的回复:
等级为0的不用计算么
不用，只计算大于0的。
桃花岛黄岛主 2016-07-11
  • 打赏
  • 举报
 回复
等级为0的不用计算么
ghx287524027 2016-07-11
  • 打赏
  • 举报
 回复 
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
ksdy138141314 2016-07-11
  • 打赏
  • 举报
 回复
引用 1 楼 wang123kui 的回复:
说句实话，真没看懂你的意思，本来以为你找叶子节点的等级，发现不是，最好说清楚
意思就是每条树形线上，只能找最靠近的这个会员。往下的就不找了。不知道这样好理解不。 对于B用户就是D,I；对于C用户就只有G 。每条线只找到最靠近那个有等级的用户就可以了
桃花岛黄岛主 2016-07-11
  • 打赏
  • 举报
 回复
说句实话，真没看懂你的意思，本来以为你找叶子节点的等级，发现不是，最好说清楚
Oracle递归树形结构查询功能
Oracle数据库在处理树形结构数据时提供了强大的递归查询功能，这种特性对于组织结构、产品分类、层级菜单等场景的应用非常广泛。递归树形结构查询主要依赖于`CONNECT BY`和`PRIOR`关键字，它们允许我们构建复杂的...
数据库设计之递归树查询
`WITH`语句配合递归选项，可以实现递归查询，非常适合处理树形结构数据。 三、`WITH RECURSIVE`语法 在支持`WITH RECURSIVE`的数据库（如PostgreSQL、SQL Server）中，递归查询的通用语法如下： ```sql WITH ...
Oracle 开发DBA SQL编写规范
- 使用递归公共表达式（Recursive Common Table Expressions, RCTE）来处理树形结构数据。 - 示例： ```sql with recursive tree as ( select id, parent_id, name from tab1 where parent_id is null union ...
sql写法_CTE
通过递归CTE，可以实现层次结构的查询，例如组织结构图、目录树等。 #### 四、CTE示例 下面通过具体的示例来说明CTE的使用方法。 1. **基础CTE示例**： ```sql WITH a AS (SELECT * FROM TA WHERE AID IS NOT ...
通过分析SQL语句的执行计划优化SQL
递归SQL是指那些在执行过程中可能调用自身或其他SQL语句的查询。正确处理递归SQL可以避免不必要的重复计算，提高查询效率。 5. **Rowsource和Predicate的优化**： Rowsource是指SQL查询中数据的来源，而Predicate...
Oracle 高级技术

3,494

社区成员

18,710

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章