SQL Join表与单个查询不同的Sum表

My issue is: I have 3 tables Invoiced, Expired, Payed, every table has these columns:

我的问题是：我有3个表已开票，已过期，已付款，每个表都有以下列：

• customer_code
• customer_code
• type
• 类型
• amount
• 量

In Oracle I'm trying to do this:

在Oracle中我试图这样做：

SELECT
    C.customer_code,
    C.type,
    SUM(C.AMOUNT)  AS AMOUNT_EXPIRED,
    SUM(F.AMOUNT)  AS AMOUNT_INVOICED,
    SUM(R.AMOUNT)  AS AMOUNT_PAYED        
  FROM Expired C
  LEFT JOIN (SELECT customer_code, ACCOUNT, SUM(AMOUNT) AS AMOUNT
               FROM Invoiced
              GROUP BY customer_code, type) F
    ON C.customer_code = F.customer_code
   AND C.type = F.type
LEFT JOIN (SELECT customer_code, type, SUM(AMOUNT) AS AMOUNT
             FROM Payed
            GROUP BY customer_code, type) R
    ON C.customer_code = R.customer_code
   AND C.type = R.type
 GROUP BY C.customer_code, C.type,
          R.customer_code, R.type, 
          F.customer_code, F.type

I get a table with columns:

我得到一个包含列的表格：

------------------------------------------------------------------------
 customer_code | type | amount_expired | amount invoiced | amount payed
------------------------------------------------------------------------ 

but the amounts are different respect to the single queries:

但金额与单个查询不同：

SELECT customer_code, type, SUM(AMOUNT) AS AMOUNT FROM expired GROUP BY customer_code, type
SELECT customer_code, type, SUM(AMOUNT) AS AMOUNT FROM invoiced GROUP BY customer_code, type
SELECT customer_code, type, SUM(AMOUNT) AS AMOUNT FROM payed GROUP BY customer_code, type

Anyone can help me?

有人可以帮帮我吗？

2 个解决方案

#1

Use union instead of join. But you should have a table to store all the customer codes and types though. And you should never use join when it comes aggregate functions. It's very confusing and usually produce incorrect results.

使用union而不是join。但是，您应该有一个表来存储所有客户代码和类型。当聚合函数出现时，你永远不应该使用join。这非常令人困惑，通常会产生不正确的结果。

with CUSTOMER as (
    select CUSTOMER_CODE, TYPE from EXPIRED group by CUSTOMER_CODE, TYPE
    union 
    select CUSTOMER_CODE, TYPE from INVOICED group by CUSTOMER_CODE, TYPE
    union 
    select CUSTOMER_CODE, TYPE from PAYED group by CUSTOMER_CODE, TYPE
)
select
    c.CUSTOMER_CODE, c.TYPE
    ,(
    select sum(AMOUNT) 
    from EXPIRED 
    where CUSTOMER_CODE = c.CUSTOMER_CODE and TYPE = c.TYPE
    ) as AMOUNT_EXPIRED
    ,(
    select sum(AMOUNT) 
    from INVOICED 
    where CUSTOMER_CODE = c.CUSTOMER_CODE and TYPE = c.TYPE
    ) as AMOUNT_INVOICED
    ,(
    select sum(AMOUNT) 
    from PAYED 
    where CUSTOMER_CODE = c.CUSTOMER_CODE and TYPE = c.TYPE
    ) as AMOUNT_EXPIRED
from CUSTOMER c
order by c.CUSTOMER_CODE, c.TYPE

#2

Your second subquery may be the reason, this may be what you expect;

你的第二个子查询可能是原因，这可能是你所期望的;

SELECT  C.customer_code, C.type, 
    SUM(C.AMOUNT)       AS AMOUNT_EXPIRED,
    SUM(F.AMOUNT)       AS AMOUNT_INVOICED,
    SUM(R.AMOUNT)       AS AMOUNT_PAYED        
FROM      Expired C
LEFT JOIN invoiced F
ON        C.customer_code= F.customer_code
AND       C.type= F.type
LEFT JOIN payed R 
ON        C.customer_code= R.customer_code
AND       C.type= R.type

GROUP BY  C.customer_code, C.type