我有以下表格,我想获取purchase_order和他的order_quantity以及每个purchase_order的接收数量总和。我知道如何从一个表中求和,但是从多个表中求和,这让我很困惑...
mysql> select * from purchase_order;
+-------------------+-------------------------+-------+---------------------+
| purchase_order_id | purchase_order | cost | created_on |
+-------------------+-------------------------+-------+---------------------+
| 1 | Dell Computer 000001256 | 10000 | 2015-02-19 22:14:52 |
| 2 | HP Computer 000001256 | 50000 | 2015-02-19 22:14:52 |
+-------------------+-------------------------+-------+---------------------+
2 rows in set (0.00 sec)
mysql> select * from purchase_order_detail;
+--------------------------+-------------------+---------+------------------+
| purchase_order_detail_id | purchase_order_id | item_id | ordered_quantity |
+--------------------------+-------------------+---------+------------------+
| 1 | 1 | 279 | 100 |
| 2 | 1 | 286 | 100 |
| 3 | 2 | 279 | 200 |
| 4 | 2 | 286 | 300 |
+--------------------------+-------------------+---------+------------------+
4 rows in set (0.00 sec)
mysql> select * from delivery_order;
+-------------------+--------------------------+-------------------+---------------------+
| delivery_order_id | purchase_order_detail_id | recieved_quantity | recieved_on |
+-------------------+--------------------------+-------------------+---------------------+
| 1 | 1 | 50 | 2015-02-19 22:22:51 |
| 2 | 2 | 50 | 2015-02-19 22:24:59 |
| 3 | 1 | 50 | 2015-02-19 22:34:14 |
| 4 | 3 | 70 | 2015-02-20 11:11:31 |
| 5 | 4 | 150 | 2015-02-20 11:11:31 |
| 6 | 3 | 90 | 2015-02-20 11:12:20 |
| 7 | 4 | 100 | 2015-02-20 11:12:20 |
| 8 | 3 | 40 | 2015-02-20 11:12:55 |
| 9 | 4 | 50 | 2015-02-20 11:12:55 |
+-------------------+--------------------------+-------------------+---------------------+
到目前为止,我有此查询,但id不会返回正确的记录。
SELECT po.purchase_order_id, SUM(pod.ordered_quantity) AS Sum_of_ordered_quantity, SUM(dor.recieved_quantity) AS Sum_of_recieved_quantity
FROM purchase_order AS po
INNER JOIN purchase_order_detail AS pod ON po.purchase_order_id = pod.purchase_order_id
INNER JOIN delivery_order AS dor ON dor.purchase_order_detail_id = pod.purchase_order_detail_id
GROUP BY po.purchase_order_id
它返回这个,
+-------------------+-------------------------+--------------------------+
| purchase_order_id | Sum_of_ordered_quantity | Sum_of_received_quantity |
+-------------------+-------------------------+--------------------------+
| 1 | 300 | 150 |
| 2 | 1500 | 500 |
+-------------------+-------------------------+--------------------------+
您可以在问题中看到,purchase_order_id 1具有200个订购数量和150个接收数量,而purchase_order_id 2具有500个ordered_quantity和500个接收数量。
最佳答案
在对具有不同连接条件的多个多对多表执行汇总求和函数时,通常会这样做。
一种方法是使用相关子查询来获取合计值,然后进行联接。像
select
po.purchase_order_id,
pod.Sum_of_ordered_quantity,
do.Sum_of_received_quantity
from purchase_order po
join
(
select purchase_order_id,sum(ordered_quantity) as Sum_of_ordered_quantity
from purchase_order_detail
group by purchase_order_id
)pod on pod.purchase_order_id = po.purchase_order_id
join
(
select
t1.purchase_order_id,
sum(t2.recieved_quantity) as Sum_of_received_quantity
from purchase_order_detail t1
join delivery_order t2 on t1.purchase_order_detail_id = t2.purchase_order_detail_id
group by t1.purchase_order_id
)do on do.purchase_order_id = po.purchase_order_id
DEMO
关于php - 从第三关系表中求和,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/28624329/