递归sql，求写法

ksdy138141314 2016-08-03 05:29:16

表结构：
with table1 as (
select 'A' as parent_no, 'B' as no from dual union all
select 'A' as parent_no, 'C' as no from dual union all
select 'B' as parent_no, 'D' as no from dual union all
select 'C' as parent_no, 'E' as no from dual union all
select 'B' as parent_no, 'F' as no from dual union all
select 'C' as parent_no, 'G' as no from dual union all
select 'G' as parent_no, 'H' as no from dual union all
select 'G' as parent_no, 'I' as no from dual union all
select 'H' as parent_no, 'J' as no from dual
),
table2 as (
select 'A' AS NO,300 AS MONEY,2400 AS ALLMONEY,1 AS RID from dual union all
select 'B' AS NO,100 AS MONEY,700 AS ALLMONEY,2 AS RID from dual union all
select 'C' AS NO,200 AS MONEY,1400 AS ALLMONEY,3 AS RID from dual union all
select 'D' AS NO,100 AS MONEY,100 AS ALLMONEY,4 AS RID from dual union all
select 'E' AS NO,400 AS MONEY,400 AS ALLMONEY,5 AS RID from dual union all
select 'F' AS NO,500 AS MONEY,500 AS ALLMONEY,6 AS RID from dual union all
select 'G' AS NO,200 AS MONEY,800 AS ALLMONEY,7 AS RID from dual union all
select 'H' AS NO,100 AS MONEY,300 AS ALLMONEY,8 AS RID from dual union all
select 'I' AS NO,300 AS MONEY,300 AS ALLMONEY,9 AS RID from dual union all
select 'J' AS NO,200 AS MONEY,200 AS ALLMONEY,10 AS RID from dual
)
table1表示用户关系，是一个二叉树的关系，每一个父节点最多只会有2个子节点。
table2表示用户的钱。MONEY 表示用户自己的钱，ALLmoneY表示该用户以及该下级所有用户的钱,RID表示用户注册先后顺序

需求：现在需要查询某个会员下面每一个层级钱最多(根据allmoney判断)，且只需要最后一个层级。
比如：A用户，子节点有B和C用户，B用户的allmoney是700，C用户的allmoney是1400，就取到C。然后C按照同样的逻辑，
继续往下取，取到G，然后H和I的是一样的，根据table2表的rid，取最早的一个，取到H，H下面只有一个J，
只有一个节点，说明另一个节点为0，取到J，J用户没有下级。最后返回J

...全文

479 20 打赏收藏转发到动态举报

写回复

用AI写文章

20 条回复

切换为时间正序

请发表友善的回复…

发表回复

三断笛 2016-08-05

打赏
举报

写数据时就把路径写进去，查的时候就不用递归了

桃花岛黄岛主 2016-08-04

打赏
举报

引用 9 楼 wildwave 的回复:

试试这个 select * from( select a.parent_no,a.no,b.money,b.allmoney,row_number()over(partition by a.parent_no order by allmoney desc) rn from table1 a,table2 b where a.no=b.no ) where connect_by_isleaf=1 start with parent_no='A' and rn=1 connect by prior no=parent_no and rn=1 还是建议用存储过程，效率会更高

还是版主，这个方法好

ksdy138141314 2016-08-04

打赏
举报

引用 14 楼 liuzhe_521 的回复:


with t1 as (
      select 'A' as parent_no, 'B' as no from dual union all
      select 'A' as parent_no, 'C' as no from dual union all
      select 'B' as parent_no, 'D' as no from dual union all
      select 'C' as parent_no, 'E' as no from dual union all
      select 'B' as parent_no, 'F' as no from dual union all
      select 'C' as parent_no, 'G' as no from dual union all
      select 'G' as parent_no, 'H' as no from dual union all
      select 'G' as parent_no, 'I' as no from dual union all
      select 'H' as parent_no, 'J' as no from dual
 )
 ,t2 as (
        select 'A' AS NO,300 AS MONEY,2400 AS ALLMONEY,1 AS RID from dual union all
        select 'B' AS NO,100 AS MONEY,700 AS ALLMONEY,2 AS RID from dual union all
        select 'C' AS NO,200 AS MONEY,1400 AS ALLMONEY,3 AS RID from dual union all
        select 'D' AS NO,100 AS MONEY,100 AS ALLMONEY,4 AS RID from dual union all
        select 'E' AS NO,400 AS MONEY,400 AS ALLMONEY,5 AS RID from dual union all
        select 'F' AS NO,500 AS MONEY,500 AS ALLMONEY,6 AS RID from dual union all
        select 'G' AS NO,200 AS MONEY,800 AS ALLMONEY,7 AS RID from dual union all
        select 'H' AS NO,100 AS MONEY,300 AS ALLMONEY,8 AS RID from dual union all
        select 'I' AS NO,300 AS MONEY,300 AS ALLMONEY,9 AS RID from dual union all
        select 'J' AS NO,200 AS MONEY,200 AS ALLMONEY,10 AS RID from dual
 )
 select max(no) keep(dense_rank first order by ph desc) res
   from (select a.no, connect_by_isleaf isleaf, connect_by_root(a.no) rt,
                 connect_by_root(allmoney) rt_am,
                 sys_connect_by_path(lpad(allmoney, 8, '0') || (100000 - rid),
                                      '->') ph
            from t1 a
            left join t2 b
              on a.no = b.no
           start with parent_no = 'A'
          connect by prior a.no = parent_no)
  where isleaf = 1

两个参数： sys_connect_by_path(lpad(allmoney, 8, '0') || (100000 - rid),'->') ph 8：要求大于等于allmoney作为字符串的最大长度 100000：要求大于等于最大顺序数

哥们这个看起来好复杂。用了好多系统函数

ksdy138141314 2016-08-04

打赏
举报

引用 16 楼 wildwave 的回复:

呃，没看到rid的要求 (partition by a.parent_no order by allmoney desc) 加上=> (partition by a.parent_no order by allmoney desc,rid) 即可

恩，这个rid我看到了。我也加上了。我另外也用到了一个办法。不过用了2层递归。

小灰狼W 2016-08-04

打赏
举报

呃，没看到rid的要求 (partition by a.parent_no order by allmoney desc) 加上=> (partition by a.parent_no order by allmoney desc,rid) 即可

liuzhe_521 2016-08-04

打赏
举报

引用 9 楼 wildwave 的回复:

试试这个 select * from( select a.parent_no,a.no,b.money,b.allmoney,row_number()over(partition by a.parent_no order by allmoney desc) rn from table1 a,table2 b where a.no=b.no ) where connect_by_isleaf=1 start with parent_no='A' and rn=1 connect by prior no=parent_no and rn=1 还是建议用存储过程，效率会更高

这种写法没有考虑rid吧，如果allmoney一样没有按rid取最早的一个

liuzhe_521 2016-08-04

打赏
举报


with t1 as (
      select 'A' as parent_no, 'B' as no from dual union all
      select 'A' as parent_no, 'C' as no from dual union all
      select 'B' as parent_no, 'D' as no from dual union all
      select 'C' as parent_no, 'E' as no from dual union all
      select 'B' as parent_no, 'F' as no from dual union all
      select 'C' as parent_no, 'G' as no from dual union all
      select 'G' as parent_no, 'H' as no from dual union all
      select 'G' as parent_no, 'I' as no from dual union all
      select 'H' as parent_no, 'J' as no from dual
 )
 ,t2 as (
        select 'A' AS NO,300 AS MONEY,2400 AS ALLMONEY,1 AS RID from dual union all
        select 'B' AS NO,100 AS MONEY,700 AS ALLMONEY,2 AS RID from dual union all
        select 'C' AS NO,200 AS MONEY,1400 AS ALLMONEY,3 AS RID from dual union all
        select 'D' AS NO,100 AS MONEY,100 AS ALLMONEY,4 AS RID from dual union all
        select 'E' AS NO,400 AS MONEY,400 AS ALLMONEY,5 AS RID from dual union all
        select 'F' AS NO,500 AS MONEY,500 AS ALLMONEY,6 AS RID from dual union all
        select 'G' AS NO,200 AS MONEY,800 AS ALLMONEY,7 AS RID from dual union all
        select 'H' AS NO,100 AS MONEY,300 AS ALLMONEY,8 AS RID from dual union all
        select 'I' AS NO,300 AS MONEY,300 AS ALLMONEY,9 AS RID from dual union all
        select 'J' AS NO,200 AS MONEY,200 AS ALLMONEY,10 AS RID from dual
 )
 select max(no) keep(dense_rank first order by ph desc) res
   from (select a.no, connect_by_isleaf isleaf, connect_by_root(a.no) rt,
                 connect_by_root(allmoney) rt_am,
                 sys_connect_by_path(lpad(allmoney, 8, '0') || (100000 - rid),
                                      '->') ph
            from t1 a
            left join t2 b
              on a.no = b.no
           start with parent_no = 'A'
          connect by prior a.no = parent_no)
  where isleaf = 1

两个参数： sys_connect_by_path(lpad(allmoney, 8, '0') || (100000 - rid),'->') ph 8：要求大于等于allmoney作为字符串的最大长度 100000：要求大于等于最大顺序数

ksdy138141314 2016-08-04

打赏
举报

ok，，看到版主贴了代码了。试了下，可行。我放到实际数据中试试，先感谢各位的帮助

ghx287524027 2016-08-04

打赏
举报

9 # 的 @ksdy138141314

z123zjf 2016-08-04

打赏
举报

看明白了，是我理解错了。。。一层一层取的。。。

z123zjf 2016-08-04

打赏
举报

题意不就是取最底层的数据吗，底层有2个节点，就取money最大的，如果只有一个，就取该值呀

小灰狼W 2016-08-04

打赏
举报

试试这个 select * from( select a.parent_no,a.no,b.money,b.allmoney,row_number()over(partition by a.parent_no order by allmoney desc) rn from table1 a,table2 b where a.no=b.no ) where connect_by_isleaf=1 start with parent_no='A' and rn=1 connect by prior no=parent_no and rn=1 还是建议用存储过程，效率会更高

ksdy138141314 2016-08-04

打赏
举报

引用 4 楼 wildwave 的回复:

2楼直接比较叶子节点的money，这样会有两个问题： 1), 跳出了一层一层比较all_money逐层递归的规则，变成了直接取叶子节点money最大的 2).首先比较的是level，那么以图为例，I节点下再挂两层，最后一层即使money只有1，也会当选实际上从性能上考虑，建议用存储过程，逻辑也会比较清晰

因为这个可能会调用频繁，看最好能用最简单的方式，想了好久也没想到

小灰狼W 2016-08-04

打赏
举报

引用 5 楼 z123zjf 的回复:

[quote=引用 4 楼 wildwave 的回复:] 2楼直接比较叶子节点的money，这样会有两个问题： 1), 跳出了一层一层比较all_money逐层递归的规则，变成了直接取叶子节点money最大的 2).首先比较的是level，那么以图为例，I节点下再挂两层，最后一层即使money只有1，也会当选实际上从性能上考虑，建议用存储过程，逻辑也会比较清晰

我的排序是level,money 先比较的是level呀[/quote] 首先比较level会有个漏洞，如果I节点下有M节点，M节点下有N节点，那么，由于N的层级比J大，不管他们的allmoney和money取值如何，N都会被选中，和题意不符所以还得想想，这题挺费脑细胞的

ksdy138141314 2016-08-04

打赏
举报

引用 1 楼 wang123kui 的回复:


with table1 as (
      select 'A' as parent_no, 'B' as no from dual union all
      select 'A' as parent_no, 'C' as no from dual union all
      select 'B' as parent_no, 'D' as no from dual union all
      select 'C' as parent_no, 'E' as no from dual union all
      select 'B' as parent_no, 'F' as no from dual union all
      select 'C' as parent_no, 'G' as no from dual union all
      select 'G' as parent_no, 'H' as no from dual union all
      select 'G' as parent_no, 'I' as no from dual union all
      select 'H' as parent_no, 'J' as no from dual
 ),
 table2 as (
        select 'A' AS NO,300 AS MONEY,2400 AS ALLMONEY,1 AS RID from dual union all
        select 'B' AS NO,100 AS MONEY,700 AS ALLMONEY,2 AS RID from dual union all
        select 'C' AS NO,200 AS MONEY,1400 AS ALLMONEY,3 AS RID from dual union all
        select 'D' AS NO,100 AS MONEY,100 AS ALLMONEY,4 AS RID from dual union all
        select 'E' AS NO,400 AS MONEY,400 AS ALLMONEY,5 AS RID from dual union all
        select 'F' AS NO,500 AS MONEY,500 AS ALLMONEY,6 AS RID from dual union all
        select 'G' AS NO,200 AS MONEY,800 AS ALLMONEY,7 AS RID from dual union all
        select 'H' AS NO,100 AS MONEY,300 AS ALLMONEY,8 AS RID from dual union all
        select 'I' AS NO,300 AS MONEY,300 AS ALLMONEY,9 AS RID from dual union all
        select 'J' AS NO,200 AS MONEY,200 AS ALLMONEY,10 AS RID from dual
 ),
 t1 as (
 select table1.*,level rn from table1 start with parent_no ='A' connect by nocycle parent_no = prior no)
 
 select * from (
 select t1.*,table2.allmoney ,row_number() over (partition by rn order by allmoney desc,rid asc) rn1 from t1,table2 where t1.no = table2.no)
 where rn1 =1

这个逻辑虽然现在出来的结果是正确的。但就像 wildwave 提到的那个问题，如果F比G的要大的话，那这里就取到F了。实际还是要去G的。另外一边就不能去比较了 PS：另外还有一个表可以用，记录了每个用户左节点和右节点是allmoney table3 as ( select 'A' AS NO,700 AS left_MONEY,1400 AS right_money from dual union all select 'B' AS NO,100 AS left_MONEY,500 AS right_money from dual union all select 'C' AS NO,400 AS left_MONEY,800 AS right_money from dual union all select 'D' AS NO,0 AS left_MONEY,0 AS right_money from dual union all select 'E' AS NO,0 AS left_MONEY,0 AS right_money from dual union all select 'F' AS NO,0 AS left_MONEY,0 AS right_money from dual union all select 'G' AS NO,300 AS left_MONEY,300 AS right_money from dual union all select 'H' AS NO,200 AS left_MONEY,0 AS right_money from dual union all select 'I' AS NO,0 AS left_MONEY,0 AS right_money from dual union all select 'J' AS NO,0 AS left_MONEY,0 AS right_money from dual )

z123zjf 2016-08-04

打赏
举报

引用 4 楼 wildwave 的回复:

2楼直接比较叶子节点的money，这样会有两个问题： 1), 跳出了一层一层比较all_money逐层递归的规则，变成了直接取叶子节点money最大的 2).首先比较的是level，那么以图为例，I节点下再挂两层，最后一层即使money只有1，也会当选实际上从性能上考虑，建议用存储过程，逻辑也会比较清晰

我的排序是level,money 先比较的是level呀

小灰狼W 2016-08-04

打赏
举报

2楼直接比较叶子节点的money，这样会有两个问题： 1), 跳出了一层一层比较all_money逐层递归的规则，变成了直接取叶子节点money最大的 2).首先比较的是level，那么以图为例，I节点下再挂两层，最后一层即使money只有1，也会当选实际上从性能上考虑，建议用存储过程，逻辑也会比较清晰

小灰狼W 2016-08-04

打赏
举报

1楼的代码思路不错，但是逻辑不太对
row_number() over (partition by rn order by allmoney desc
这里，按照层级来对allmoney进行排序，实际上，以图中的为例，如果F节点的allmoney比G节点更大，则会选中F节点。而在这一层级，我们只能在E和G之间比较，F应该过滤
我暂时也没想出更好的办法

z123zjf 2016-08-04

打赏
举报

t as(select t2.no,t1.parent_no,t2.money,t2.allmoney,t2.rid from table1 t1 right join table2 t2 on t1.no=t2.no), m as(select lpad(no,level*2,' ') no,level lev,connect_by_root no rno,money from t connect by parent_no=prior no) select rno,max(no)keep(dense_rank last order by lev,money) from m group by rno having count(1)>1 凑的，还有更好的写法吧

桃花岛黄岛主 2016-08-04

打赏
举报


with table1 as (
      select 'A' as parent_no, 'B' as no from dual union all
      select 'A' as parent_no, 'C' as no from dual union all
      select 'B' as parent_no, 'D' as no from dual union all
      select 'C' as parent_no, 'E' as no from dual union all
      select 'B' as parent_no, 'F' as no from dual union all
      select 'C' as parent_no, 'G' as no from dual union all
      select 'G' as parent_no, 'H' as no from dual union all
      select 'G' as parent_no, 'I' as no from dual union all
      select 'H' as parent_no, 'J' as no from dual
 ),
 table2 as (
        select 'A' AS NO,300 AS MONEY,2400 AS ALLMONEY,1 AS RID from dual union all
        select 'B' AS NO,100 AS MONEY,700 AS ALLMONEY,2 AS RID from dual union all
        select 'C' AS NO,200 AS MONEY,1400 AS ALLMONEY,3 AS RID from dual union all
        select 'D' AS NO,100 AS MONEY,100 AS ALLMONEY,4 AS RID from dual union all
        select 'E' AS NO,400 AS MONEY,400 AS ALLMONEY,5 AS RID from dual union all
        select 'F' AS NO,500 AS MONEY,500 AS ALLMONEY,6 AS RID from dual union all
        select 'G' AS NO,200 AS MONEY,800 AS ALLMONEY,7 AS RID from dual union all
        select 'H' AS NO,100 AS MONEY,300 AS ALLMONEY,8 AS RID from dual union all
        select 'I' AS NO,300 AS MONEY,300 AS ALLMONEY,9 AS RID from dual union all
        select 'J' AS NO,200 AS MONEY,200 AS ALLMONEY,10 AS RID from dual
 ),
 t1 as (
 select table1.*,level rn from table1 start with parent_no ='A' connect by nocycle parent_no = prior no)
 
 select * from (
 select t1.*,table2.allmoney ,row_number() over (partition by rn order by allmoney desc,rid asc) rn1 from t1,table2 where t1.no = table2.no)
 where rn1 =1