SQL CTE递归查询

usermoney表：

需求:需要查询各级用户的money总和，比如，查询userid=2的用户的money总和需要包括他的子级以及衍生子级的money总和，以此类推

第一步：

with cte(userid,money,level_) as
(
SELECT userid, money, 1 AS level_ FROM usermoney
union all
select m.userid,m.money,a.level_ +1
from cte a
inner join usermoney m
on a.userid=m.pid
)


select * from cte

通过cte表的递归可以求出各个userid的递归深度（level_），结果如图：

我们可以再加一列path_ 来直观的看出父子节点的关系：

with cte(userid,money,level_,path_) as
(
SELECT userid, money, 1 AS level_ ,'' as path_ FROM usermoney
union all
select m.userid,m.money,a.level_ +1,m.pid||','||a.path_
from cte a
inner join usermoney m
on a.userid=m.pid
)


select * from cte

结果如下：

需要注意的是 我们要取递归深度最深的记录，例如userid=4的记录既有level_=2的记录也有level_=3的记录，我们要取level_=3的那条记录 即path_=2,1, 那一条

计算总和就应该是userid=4的money+userid=2的money+userid=1的money

 

方法2：oracle：

select userid,money from usermoney
start with userid=7
connect by prior pid=userid

select sum(money)
from usermoney
start with userid=7
connect by prior pid=userid