mysql - mysql内连接四个表

Question

我有四个表。我想要四个表的内连接结果。我的查询是:

select 
    customer.id,
    customer.name,
    customer.opeing_balance,
    customer.opening_balance_type,
    sum(sale.total) as sum_total,
    sum(receipt.net_amount) as sum_net_amount, 
    sum(sale_return.total_return) as sum_sale_return 
from customer 
    inner join sale on customer.id=sale.cust_id 
    inner join receipt on customer.id=receipt.cust_id 
    inner join sale_return on customer.id=sale_return.cust_id 
group by customer.id

表结构

customer

id   name   opening_balance    opening_balance_type
26   neena    50                  debit
27   shan     50                  debit


sale

cust_id         total
27               50
27               50
26               60
26               40

receipt

cust_id     net_amount
 27            10
 27            10
 26            50
 26             10

sale_return

cust_id         total_return
 27                10
 27                20
 26                15
 26                20

结果

名称 opening_balance opening_balance_type sum_total sum_net_amount sum_sale_return
尼娜 50 借方 100 60 35shan 50 借方 100 20 30

我想要每个客户的customer_name，opening_balance，opening_balance_type，total总和，net_amount总和，total_retun总和。有人能给点帮助吗？

Answer 1

好的，试试这个:

select 
    customer.id,
    customer.name,
    customer.opeing_balance,
    customer.opening_balance_type,
    s.total,
    r.net_amount,
    s_r.total_return
from customer INNER JOIN
    (SELECT cust_id,SUM(total) total FROM sale GROUP BY cust_id) s ON s.cust_id = customer.id INNER JOIN
    (SELECT cust_id,SUM(net_amount) net_amount FROM receipt GROUP BY cust_id) r ON r.cust_id = customer.id INNER JOIN
    (SELECT cust_id,SUM(total_return) total_return FROM sale_return GROUP BY cust_id) s_r ON s_r.cust_id = customer.id

SQLFiddle demo here