mysql 树状图数据 递归升级版问题

Question

 一条SQL 可以解决吗。可以请直接上SQL

Answer 1

重复问题

mysql 树状图数据递归。升级版问题-大数据-CSDN问答 CSDN问答为您找到mysql 树状图数据递归。升级版问题相关问题答案，如果想了解更多关于mysql 树状图数据递归。升级版问题 mysql、sql、数据库 技术问题等相关问答，请访问CSDN问答。 https://ask.csdn.net/questions/7645060?spm=1005.2025.3001.5141

--测试数据
create table test_20220217 
(id int,name VARCHAR(2),p_id int,money int);
insert into test_20220217 values(1,'A',-1,10);
insert into test_20220217 values(2,'B',1,15);
insert into test_20220217 values(3,'C',1,12);
insert into test_20220217 values(4,'D',2,11);
insert into test_20220217 values(5,'E',2,13);
insert into test_20220217 values(6,'F',3,14);
insert into test_20220217 values(7,'G',4,20);
insert into test_20220217 values(8,'H',4,31);
insert into test_20220217 values(9,'I',5,55);

--查询
WITH RECURSIVE  cte as (select t.*,1 lvl,cast(null as  int) third_node from test_20220217 t where id=1
union all 
select t.*,lvl+1,case when lvl+1=3 then t.id when lvl+1>3 then cte.id end  from test_20220217 t,cte where cte.id=t.p_id
)
select third_node,sum(money) from cte where third_node is not null group by third_node

查询效果