从多个表中获取数据并加上数量 [英] Get data from multiple tables with sum the quantity

问题描述

我有以下表格，我想获取每个purchase_order的purchase_order及其order_quantity和收到的数量总和。我知道如何从单个表中总结数量，但是从多个表中总结，这让我感到困惑......

I have following tables, i want to fetch the purchase_order and his order_quantity and sum of recieved quantity for each purchase_order. i know how to sum the quantity from single table but from multiple tables, it is confusing me alot...

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 |
+-------------------+--------------------------+-------------------+---------------------+

推荐答案

尝试：

Try:

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

注意：它是直接写的;）



注2 ：可能需要将连接类型更改为 LEFT JOIN 。

这篇关于从多个表中获取数据并加上数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！