22,209
社区成员
发帖
与我相关
我的任务
分享
WITH
/* 测试数据
table1(id,topid,money,isLast)AS(
SELECT 1,0,0,0 UNION ALL
SELECT 2,1,0,0 UNION ALL
SELECT 3,1,100,1 UNION ALL
SELECT 4,2,20,1 UNION ALL
SELECT 5,2,30,1
), */
a AS (
-- 取最后一级的金额
SELECT id,topid,money
FROM table1
WHERE isLast = 1
UNION ALL -- 每个金额的每个上级都递归生成一条明细
SELECT p.id,p.topid,c.money
FROM table1 p
JOIN a c
on c.topid = p.id
)
,b AS (
SELECT id,
SUM(money) money
FROM a
GROUP BY id
)
SELECT *
FROM b
ORDER BY id
id money
----------- -----------
1 150
2 50
3 100
4 20
5 30