mysql - 使用 View 从多个表中获取数据

Question

我有下表，

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 |
+-------------------+--------------------------+-------------------+---------------------+
9 rows in set (0.00 sec)

mysql> select * from stock;
+----------+-------------------+------------+----------+
| stock_id | delivery_order_id | project_id | quantity |
+----------+-------------------+------------+----------+
|        1 |                 1 |          1 |       30 |
|        2 |                 1 |          2 |       20 |
|        3 |                 2 |          1 |       50 |
|        4 |                 3 |          1 |       40 |
|        5 |                 3 |          2 |       10 |
+----------+-------------------+------------+----------+

我想获取 item_id = 279 的所有购买订单及其库存数量。

目标是创建 View ，在其中我只需传递 item_id，它就会获取所有 buy_order 的列表，其中 item_id 将作为输入参数及其库存总量。

所以，到目前为止我已经写了这个查询，我是 mysql 和 View 的新手

select po.purchase_order_id, po.purchase_order from purchase_order po, purchase_order_detail pod where po.purchase_order_id = pod.purchase_order_id and pod.item_id = 279;
+-------------------+-------------------------+
| purchase_order_id | purchase_order          |
+-------------------+-------------------------+
|                 1 | Dell Computer 000001256 |
|                 2 | HP Computer 000001256   |
+-------------------+-------------------------+

但它想要这样的东西，

+-------------------+-------------------------+----------+-----------+
| purchase_order_id | purchase_order          | item_id  | quantity  |
+-------------------+-------------------------+----------+-----------+
|                 1 | Dell Computer 000001256 | 279      |    100    +
|                 2 | HP Computer 000001256   | 279      |     0     +
+-------------------+-------------------------+----------+-----------+

Answer 1

尝试这个未经测试的查询:

select po.purchase_order_id, po.purchase_order, sum(s.quantity) 
from purchase_order po 
join  purchase_order_detail pod on po.purchase_order_id = pod.purchase_order_id
join  delivery_order do on do.purchase_order_id = pod.purchase_order_id
join  stock s on s.delivery_order_id  = do.delivery_order_id 
where pod.item_id = 279
group by po.purchase_order_id, po.purchase_order;