在其他表MySQL中的孩子的总价

Question

我有两个表，一个存储数据的子级和父级，其他路径和后代

+----------+------------+-----------+
| userid   |    parent  |    price  |
+----------+------------+------------
| 1        |    null    |      20   | 
| 2        |      1     |      20   | 
| 3        |      1     |      20   | 
| 4        |      2     |      20   | 
| 5        |      2     |      20   | 
| 6        |      3     |      20   | 
| 7        |      4     |      20   | 
+----------+------------+-----------+

我需要获取所有具有父代1的用户ID，然后获取其他表中的子代，并按用户ID总和价格分组

+-------------+---------------+-------------+
| ancestor_id | descendant_id | path_length |
+-------------+---------------+-------------+
|           1 |             1 |           0 |
|           1 |             2 |           1 |
|           1 |             3 |           1 |
|           1 |             4 |           2 |
|           1 |             5 |           2 |
|           1 |             6 |           2 |
|           1 |             7 |           3 |
|           2 |             2 |           0 |
|           2 |             4 |           1 |
|           2 |             5 |           1 |
|           2 |             7 |           2 |
|           3 |             3 |           0 |
|           3 |             6 |           1 |
|           4 |             4 |           0 |
|           4 |             7 |           1 |
|           5 |             5 |           0 |
|           6 |             6 |           0 |
|           7 |             7 |           0 |
+-------------+---------------+-------------+

我已经查询了所有孩子的总和

select 
sum(b.price)

from webineh_prefix_nodes_paths_tmp a

    join webineh_prefix_nodes_tmp b on (b.userid = a.descendant_id)

where a.ancestor_id = 1

这项工作很好，但父母总和为1

我需要显示直接儿童的波纹管结果（2,3）

 +----------+------------+-
    | userid   |    total   |
    +----------+------------+
    | 2        |    80      |
    | 3        |    40      |
    +----------+------------+

也在创建sqlfiddle我的问题http://sqlfiddle.com/#!9/9415ed/2

Answer 1

试试这个;）

select ancestor_id as userid, sum(b.price) as total
from webineh_prefix_nodes_paths_tmp a 
join webineh_prefix_nodes_tmp b 
on b.userid = a.descendant_id
where a.ancestor_id in (select userid from webineh_prefix_nodes_tmp where parent = 1)
group by ancestor_id

已编辑

select ancestor_id as userid, sum(b.price) as total
from webineh_prefix_nodes_paths_tmp a 
join webineh_prefix_nodes_tmp b 
on b.userid = a.descendant_id
inner join webineh_prefix_nodes_tmp c
on a.ancestor_id = c.userid
and c.parent = 1
group by ancestor_id