如何联接三个表以求和 [英] How to join three tables to get Sum

问题描述

我有三个表:产品"，购买"，发票"

I have three tables: Products, Purchase, Invoice

产品表:
Producct_no   名称
1               nbsp; A
2               nbsp; B
3              nbsp; C

Product table:
Producct_no     Name
1                        A
2                        B
3                        C

购买表:
Purchase_no   Product_no   数量
001             1              81
002nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; 2              150
003             3              10

Purchase table:
Purchase_no    Product_no    Qty
001                    1                     81
002                    2                     150
003                    3                     10

发票表:
发票_否   Product_no   数量
001              1                        20
002nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; 2                             10
003             3                              10

Invoice table:
Invoice_no    Product_no    Qty
001                    1                 20
002                    2                 10
003                    3                 10

我想获取每种产品的购买数量和发票数量，我使用了以下查询

I want to get each product's purchase quantity and invoice quantity, I used following query

    SELECT PR.product_no, sum(P.qty),sum(I.qty)
    FROM products PR 
    LEFT  JOIN invoice I ON I.product_no=PR.product_no
    LEFT  JOIN purchase P ON P.product_no=PR.product_no
    group by PR.product_no

product_no  sum(P.qty)    sum(I.qty)
001             162            160
002nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; 150            50
003              10                                 10

product_no    sum(P.qty)    sum(I.qty)
001                    162             160
002                    150             50
003                    10               10

预期结果
product_no  sum(P.qty)    sum(I.qty)
001             81               20
002nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; nbsp; 150            10
003              10                                 10

Expected results
product_no    sum(P.qty)    sum(I.qty)
001                    81             20
002                    150             10
003                    10               10

我的查询给我错误的答复(数量总和错误)，请帮助我更正我的查询以正确获得结果.谢谢

My query is giving me wrong response (sum of quantities are wrong), please help me to correct my query to get the results properly. thanks

推荐答案

根据提供的信息，我认为您的样本数据并不是您真正拥有的.我最好的猜测是，您的查询正在对这两个连接中的一个或两个进行扇出操作，这会使您的总和混乱.您需要分别对它们进行求和，否则，那些联接中的任何一个联接上的其他行都会扇出另一个联接，从而将结果复制到总和中.这在您的结果中很明显，因为001看起来是两倍(即使您的示例数据没有显示出来).

I don't think your sample data is really what you have based on the information provided. My best guess here is that your query is doing a fan-out on either or both of those joins which is messing up your sums. You need to sum them separately, else additional rows in either on of those joins will fan out the other join, duplicating your results in the sum. This is evident in your result since 001 looks to be double (even though your sample data doesn't show it).

这样的事情将确保总和彼此独立:

Something like this would ensure sums independent of each other:

SELECT PR.product_no, 
    ( SELECT sum(I.qty)
      FROM invoice I 
      WHERE I.product_no=PR.product_no ) invoice_qty,
    ( SELECT sum(P.qty)
      FROM purchase P 
      WHERE P.product_no=PR.product_no ) purchase_qty
FROM products PR 

这篇关于如何联接三个表以求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！