我有两个表，一个存储数据的子级和父级，其他路径和后代
+----------+------------+-----------+ | 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
已编辑
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
