一个多表分组统计SQL语句该怎么组合？

Question

我现在有4个表
1.用户表
2.充值记录表
3.兑换表
4.转账记录表
现在我要统计每个用户的充值总金额，兑换总金额，转账总金额
我自己的写SQL语句如下：
select u.id,u.email,u.username,sum(p.moneynumber) as paymoney,sum(e.exchangemoney) as exchangemoney,sum(c.carrymoney) as carrymoney from jw_jwuser u,jw_paymoney p,jw_exchange e,jw_carrymoney c where p.uid=u.id and e.uid=u.id and c.outuid=u.id group by u.username
打印如下结果：
Array
(
    [0] => Array
        (
            [id] => 57
            [email] => [email protected]
            [username] => rr1111
            [paymoney] => 200
            [exchangemoney] => 520
            [carrymoney] => 440
        )    [1] => Array
        (
            [id] => 71
            [email] => [email protected]
            [username] => gg1111
            [paymoney] => 560
            [exchangemoney] => 1080
            [carrymoney] => 600
        ))
但是统计的数据不正确，后又写SQl语句：select u.id,u.email,u.username,p.moneynumber,e.exchangemoney,c.carrymoney from jw_jwuser u,jw_paymoney p,jw_exchange e,jw_carrymoney c where p.uid=u.id and e.uid=u.id and c.outuid=u.id
打印如下：
Array
(
    [0] => Array
        (
            [id] => 71
            [email] => [email protected]
            [username] => gg1111
            [moneynumber] => 80
            [exchangemoney] => 120
            [carrymoney] => 100
        )    [1] => Array
        (
            [id] => 71
            [email] => [email protected]
            [username] => gg1111
            [moneynumber] => 60
            [exchangemoney] => 120
            [carrymoney] => 100
        )    [2] => Array
        (
            [id] => 71
            [email] => [email protected]
            [username] => gg1111
            [moneynumber] => 80
            [exchangemoney] => 150
            [carrymoney] => 100
        )    [3] => Array
        (
            [id] => 71
            [email] => [email protected]
            [username] => gg1111
            [moneynumber] => 60
            [exchangemoney] => 150
            [carrymoney] => 100
        )    [4] => Array
        (
            [id] => 71
            [email] => [email protected]
            [username] => gg1111
            [moneynumber] => 80
            [exchangemoney] => 120
            [carrymoney] => 50
        )    [5] => Array
        (
            [id] => 71
            [email] => [email protected]
            [username] => gg1111
            [moneynumber] => 60
            [exchangemoney] => 120
            [carrymoney] => 50
        )    [6] => Array
        (
            [id] => 71
            [email] => [email protected]
            [username] => gg1111
            [moneynumber] => 80
            [exchangemoney] => 150
            [carrymoney] => 50
        )    [7] => Array
        (
            [id] => 71
            [email] => [email protected]
            [username] => gg1111
            [moneynumber] => 60
            [exchangemoney] => 150
            [carrymoney] => 50
        )    [8] => Array
        (
            [id] => 57
            [email] => [email protected]
            [username] => rr1111
            [moneynumber] => 20
            [exchangemoney] => 50
            [carrymoney] => 50
        )    [9] => Array
        (
            [id] => 57
            [email] => [email protected]
            [username] => rr1111
            [moneynumber] => 30
            [exchangemoney] => 50
            [carrymoney] => 50
        )    [10] => Array
        (
            [id] => 57
            [email] => [email protected]
            [username] => rr1111
            [moneynumber] => 20
            [exchangemoney] => 80
            [carrymoney] => 50
        )    [11] => Array
        (
            [id] => 57
            [email] => [email protected]
            [username] => rr1111
            [moneynumber] => 30
            [exchangemoney] => 80
            [carrymoney] => 50
        )    [12] => Array
        (
            [id] => 57
            [email] => [email protected]
            [username] => rr1111
            [moneynumber] => 20
            [exchangemoney] => 50
            [carrymoney] => 60
        )    [13] => Array
        (
            [id] => 57
            [email] => [email protected]
            [username] => rr1111
            [moneynumber] => 30
            [exchangemoney] => 50
            [carrymoney] => 60
        )    [14] => Array
        (
            [id] => 57
            [email] => [email protected]
            [username] => rr1111
            [moneynumber] => 20
            [exchangemoney] => 80
            [carrymoney] => 60
        )    [15] => Array
        (
            [id] => 57
            [email] => [email protected]
            [username] => rr1111
            [moneynumber] => 30
            [exchangemoney] => 80
            [carrymoney] => 60
        ))
发现统计的有重复的。
像这样的情况，SQL语句该怎么组合？

Answer 1

select a.id,a.email,a.username, b.chong_zh ,c.dui_h,d.zhuan_zh  
from  
jw_jwuser  a,
(select id,sum(moneynumber) as chong_zh from jw_paymoney group by id) b,
(select id,sum(exchangemoney) as dui_h   from jw_exchange group by id) c,
(select id,sum(carrymoney) as zhuan_zh from jw_carrymoney group by id) d
where a.id=b.id and a.id=c.id and a.id=d.id

Answer 2

select a.id,a.email,a.username, b.chong_zh ,c.dui_h,d.zhuan_zh  
from  
jw_jwuser  a left join
(select id,sum(moneynumber) as chong_zh from jw_paymoney group by id) b on a.id=b.id
left join 
(select id,sum(exchangemoney) as dui_h   from jw_exchange group by id) c on a.id=c.id 
left join 
(select id,sum(carrymoney) as zhuan_zh from jw_carrymoney group by id) d on a.id=d.id这么写就好了，要不然没有记录的id查不出来~~

Answer 3

大神，你QQ多少啊？？？
我刚刚修改完以后，你给的语句，OK了，谢谢你啊