postgresql - 如何在 POStgresql 中对不同表的值求和？

Question

我有4张 table

billing_billmanagement
-------------------------

id    | date         | branch_id 
---------------------------------
1     | 2019-03-01   | 1         
2     | 2019-03-02   | 1          
3     | 2019-03-03   | 1       
4     | 2019-03-04   | 1          
6     | 2009-03-05   | 1  

billing_customerproductbill
-------------------------------      
id    | bill_id | product_id | discounted_price | product_qty
-------------------------------------------------------------
1     | 111     | 1          |  500             |  1        
2     | 112     | 2          |  200             |  2  
3     | 112     | 2          |  600             |  1
4     | 113     | 3          |  400             |  1
6     | 113     | 3          |  100             |  1

users_usercommission
-------------------------------      
id    | bill_id | product_id |staff_user_id | comission_amount
----------------------------------------------------------------
1     | 111     | 1          |  001         |  200        
2     | 112     | 2          |  002         |  300
3     | 112     | 2          |  002         |  400
4     | 113     | 3          |  005         |  500
6     | 113     | 3          |  005         |  600

users_staffuser
--------------- 
id     | name  
-------------- 
001    | Ali         
002    | Hsssan   
003    | Farhan   

当我使用这个查询时，我的结果是 Sum Double。我不知道是什么问题。我尝试了很多来解决这个问题，但我不能。这是我展示的有问题的表结构。有人可以检查这个查询吗。

select  au.name, sum(bcp.product_qty), Sum(bcp.discounted_price), 
Sum(uc.commission_amount)
from billing_customerproductbill bcp
inner join users_usercommission uc on bcp.product_id = uc.product_id
and bcp.bill_id = uc.bill_id
inner join billing_billmanagement bb on bcp.bill_id = bb.id
inner join users_staffuser us on uc.staff_user_id = us.id
where bb.date between '2019-03-01' and '2019-03-05'
 and bb.branch_id = '1'
group by au.first_name 
order by 1 ASC

这是我想要的结果

-------------------------------      
Name  | product_qty | discounted_price| commission_amount
-------------------------------------------------------------
Ali   | 1           | 500             |  200                   
Hsssan| 3           | 800             |  700           

Answer 1

由于你的表中冗余字段过多，在join之前，每一步都需要保证join key的唯一性，我想下面的SQL会为你返回正确的结果(顺便说一下，这里的 with 子句就像 SQL 中的子查询，但会给我们一个更好更清晰的代码风格):

   with billing_customerproductbill_sum as (
    select
        bill_id,
        product_id,
        sum(discounted_price) as discounted_price_sum,
        sum(product_qty) as product_qty_sum
    from
        billing_customerproductbill
    group by
        bill_id,
        product_id
    )
    ,users_usercommission_sum as (
    select
        bill_id,
        product_id,
        staff_user_id,
        sum(comission_amount) as comission_amount_sum
    from
        users_usercommission
    group by
        bill_id,
        product_id,
        staff_user_id
    )
    select
        u.name,
        sum(product_qty_sum) as product_qty,
        sum(discounted_price_sum) as discounted_price,
        sum(comission_amount_sum) as comission_amount
    from
        users_staffuser u
    join
        users_usercommission_sum uc on u.id = uc.staff_user_id
    join
        billing_customerproductbill_sum bc on uc.bill_id = bc.bill_id and uc.product_id = bc.product_id
    group by
        u.name