我有两个表，一个存储数据子级和父级层次结构以及其他路径和后代
+----------+------------+-----------+
| 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
尝试这个;）
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
SQLFiddle 演示
Edited
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
SQLFiddle 演示
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
