php - 使用求和函数时，我的查询返回单条记录

Question

我有以下表结构，关系是一个 purchase_order 有多个 purchase_order_detail 和一个 purchase_order_detail 有多个 delivery_order;采购订单

+-------------------+-------------------------+
| purchase_order_id | purchase_order          |
+-------------------+-------------------------+
|                29 | Dell Computer 000001256 |
|                31 | Dell Computer 000001257 |
+-------------------+-------------------------+

purchase_order_detail

+--------------------------+-------------------+---------+------------------+
| purchase_order_detail_id | purchase_order_id | item_id | ordered_quantity |
+--------------------------+-------------------+---------+------------------+
|                       26 |                29 |     279 |              100 |
|                       27 |                29 |     286 |              100 |
|                       28 |                29 |     287 |              100 |
|                       29 |                31 |     279 |               75 |
|                       30 |                31 |     286 |               85 |
+--------------------------+-------------------+---------+------------------+

delivery_order

+-------------------+--------------------------+-------------------+
| delivery_order_id | purchase_order_detail_id | recieved_quantity |
+-------------------+--------------------------+-------------------+
|                30 |                       26 |                50 |
|                31 |                       27 |                50 |
|                32 |                       28 |                50 |
|                33 |                       29 |                25 |
|                34 |                       30 |                35 |
+-------------------+--------------------------+-------------------+

我想在 purchase_order_detail 表中获取 item_id 的 purchase_order 和总接收数量，我写了这个查询，但是它返回了不正确的记录

SELECT po.created_on, po.purchase_order_id, po.purchase_order, i.item_name, SUM( do.recieved_quantity ) AS quantity
FROM purchase_order po, purchase_order_detail pod, delivery_order do , item i
WHERE i.item_id = pod.item_id
AND po.purchase_order_id = pod.purchase_order_id
AND pod.purchase_order_detail_id = do.purchase_order_detail_id
AND pod.item_id =286

+---------------------+-------------------+-------------------------+-----------+----------+
| created_on          | purchase_order_id | purchase_order          | item_name | quantity |
+---------------------+-------------------+-------------------------+-----------+----------+
| 2015-02-23 13:49:40 |                29 | Dell Computer 000001256 | Lenovo    |       85 |
+---------------------+-------------------+-------------------------+-----------+----------+

如您所见，item_id 286 对应两个 purchase_orders。

Answer 1

您错过了 group by 子句:

SELECT po.created_on, po.purchase_order_id, po.purchase_order, i.item_name, SUM( do.recieved_quantity ) AS quantity
FROM purchase_order po, purchase_order_detail pod, delivery_order do , item i
WHERE i.item_id = pod.item_id
AND po.purchase_order_id = pod.purchase_order_id
AND pod.purchase_order_detail_id = do.purchase_order_detail_id
AND pod.item_id =286
group by po.created_on, po.purchase_order_id, po.purchase_order, i.item_name