Question

请问大家，这样一条SQL语句应该怎么写？

需要查询各级用户的money总和，查询userid=1的用户的money总和，因为他的pid=0，即是查询所有用户的money总和，查询userid=2的用户的money总和需要包括他的子级以及衍生子级的money总和，以此类推，请问大家，这样一条SQL语句应该怎么写？

Answer 1

sqlserver中的做法如下：
/****** Script for SelectTopNRows command from SSMS ******/
/****** Script for SelectTopNRows command from SSMS ******/
with subtry(id,userid,userid1,pid,money) as
(
select id,userid,userid userid1,pid,money from table1 b
union all
select b.id,subtry.userid,b.userid,b.pid,b.money from table1 b ,subtry
where b.pid=subtry.userid1
)
select userid
, (select sum(money) from (select distinct * from subtry) b where b.userid=a.userid
) money
from table1 a
group by userid
order by userid

Answer 2

with userlist as
(
select userid, pid, sum(money) money from bi_bookcity.haha_813 group by userid, pid order by userid
)
select userid,pid
, (select sum(money) from userlist connect by pid = prior userid start with userid = t0.userid) money
from userlist t0
order by userid