在其他表中mysql的总和价格 [英] sum price of childs in other table mysql
本文介绍了在其他表中mysql的总和价格的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有两个表一存储数据的子和父层次结构和其他路径和后代
+ ------ ---- + ------------ + ----------- +
|用户ID |父母|价格|
+ ---------- + ------------ + ------------
| 1 | null | 20 |
| 2 | 1 | 20 |
| 3 | 1 | 20 |
| 4 | 2 | 20 |
| 5 | 2 | 20 |
| 6 | 3 | 20 |
| 7 | 4 | 20 |
+ ---------- + ------------ + ----------- +
我需要获取所有userid，然后在其他表中获得后代，然后按用户标价总价计算
+ ------------- + --------------- + ---- --------- +
| 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 |
+ ------------- + --------------- + ------------- +
我有查询它将所有孩子放在一起
<$
$（b.userid = a .descendant_id）
其中a.ancestor_id = 1
总和父母1
我需要显示孩子直接的结果（2,3）
+ ---------- + ------------ + -
|用户ID |总| |
+ ---------- + ------------ +
| 2 | 80 |
| 3 | 40 |
+ ---------- + ------------ +
也在创建sqlfiddle我的问题 http://sqlfiddle.com/# ！9 / 9415ed / 2
解决方案
试试这个;）
选择ancestor_id作为userid，sum（b.price）作为总数
from webineh_prefix_nodes_paths_tmp a
join webineh_prefix_nodes_tmp b
on b.userid = a .descendant_id
其中a.ancestor_id in（从webineh_prefix_nodes_tmp选择userid，其中parent = 1）
group by ancestor_id
已编辑
选择ancestor_id作为userid，sum（b.price）作为总数
from webineh_prefix_nodes_paths_tmp a
join webineh_prefix_nodes_tmp b
on b.userid = a.descendant_id
内部连接webineh_prefix_nodes_tmp c
在a上.ancestor_id = c.userid
和c.parent = 1
by ancestor_id
I have two table one store data child and parent hierarchy and other paths and descendant
+----------+------------+-----------+
| userid | parent | price |
+----------+------------+------------
| 1 | null | 20 |
| 2 | 1 | 20 |
| 3 | 1 | 20 |
| 4 | 2 | 20 |
| 5 | 2 | 20 |
| 6 | 3 | 20 |
| 7 | 4 | 20 |
+----------+------------+-----------+
I need to get all userid with parent 1 then get descendant in other table and group by userid sum prices
+-------------+---------------+-------------+
| 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 |
+-------------+---------------+-------------+
I have query it sum all childs together
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
this work fine but total sum parent 1
I need to show bellow result for child direct (2,3)
+----------+------------+-
| userid | total |
+----------+------------+
| 2 | 80 |
| 3 | 40 |
+----------+------------+
also in create sqlfiddle my question http://sqlfiddle.com/#!9/9415ed/2
解决方案
Try this;)
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
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
这篇关于在其他表中mysql的总和价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
