查询返回重复记录 [英] Query Returns duplicate records

问题描述

我有以下表格，

I have following tables,

+-------------------+-------------------------+
| purchase_order_id | purchase_order          |
+-------------------+-------------------------+
|                 1 | Dell Computer 000001256 |
|                 2 | HP Computer 000001256   |
|                 3 | Dell Computer 000001259 |
+-------------------+-------------------------+
3 rows in set (0.00 sec)

mysql> select purchase_order_detail_id, purchase_order_id, item_id, ordered_quantity 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 |
|                       23 |                 1 |     287 |              100 |
+--------------------------+-------------------+---------+------------------+
5 rows in set (0.00 sec)

mysql> select delivery_order_id, purchase_order_detail_id, recieved_quantity from delivery_order;+-------------------+--------------------------+-------------------+
| delivery_order_id | purchase_order_detail_id | recieved_quantity |
+-------------------+--------------------------+-------------------+
|                 1 |                        1 |                50 |
|                 2 |                        2 |                50 |
|                 3 |                        1 |                50 |
|                 4 |                        3 |                70 |
|                 5 |                        4 |               150 |
|                 6 |                        3 |                90 |
|                 7 |                        4 |               100 |
+-------------------+--------------------------+-------------------+

并希望获得购买订单中物品的总收货数量。

and wanted to get total received quantity of a item against an purchase order.

select do.delivery_order_id, do.recieved_quantity from purchase_order po, purchase_order_detail pod, delivery_order do where do.purchase_order_detail_id = pod.purchase_order_detail_id and pod.purchase_order_id = 1 and pod.item_id = 279;
+-------------------+-------------------+
| delivery_order_id | recieved_quantity |
+-------------------+-------------------+
|                 1 |                50 |
|                 3 |                50 |
|                 1 |                50 |
|                 3 |                50 |
|                 1 |                50 |
|                 3 |                50 |
+-------------------+-------------------+

它应该只返回前两行，如果我总结收到的数量则更多，然后它返回300.

我很困惑我错在哪里..

it should returns the only the first two rows, more if i sum the received quantity then it returns the 300.
I am confuse about where i get it wrong..

推荐答案

您没有加入 purchase_order 和 purchase_order_detail 。



你应该有

You don't join purchase_order and purchase_order_detail.

Should you have

...
from purchase_order po, 
     purchase_order_detail pod, 
     delivery_order do 
where po.purchase_order_id = pod.purchase_order_id 
and  do.purchase_order_detail_id = pod.purchase_order_detail_id 
...

这篇关于查询返回重复记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！