关于MySQL多表联查并求和计算

[zjxhdlj]

是这样的一个情况 这里有三张表 分别是用户user，订单 trade ，和资金记录 user_money_record
现在要根据用户来统计他们的订单总金额和账户余额以及充值总金额

user表
id realname money
1 张xx 100
2 王xx 500
3 李xx 1500

trade
id uid realmoney
1 2 5000
2 1 1000
3 2 5000
4 3 10000
5 1 3500

user_money_record
id uid money
1 2 5000
2 1 5000
3 2 2000
4 2 3000
5 3 5000
6 3 5000

表结构及数据如下
求统计他们的订单总金额和账户余额以及充值总金额

[致命的西瓜]

订单总金额和账户余额以及充值总金额分别对应哪个表的哪个字段

[zjxhdlj]

引用 1 楼 wangjian0228 的回复:

订单总金额和账户余额以及充值总金额分别对应哪个表的哪个字段

上面说的很清楚了哦 user对应用户表 trade 对应的是订单 user_money_record 对应的用户充值的记录

[rucypli]

select A.id,A.realname,sum(B.realmoney),sum(C.money) from user A,trade B ,user_money_record C where A.id=B.uid and A.id=C.uid group by A.id

[致命的西瓜]

引用 3 楼 rucypli 的回复:

select A.id,A.realname,sum(B.realmoney),sum(C.money) from user A,trade B ,user_money_record C where A.id=B.uid and A.id=C.uid group by A.id

他有余额的定义，这个不能这么算的

[zjxhdlj]

引用 3 楼 rucypli 的回复:

select A.id,A.realname,sum(B.realmoney),sum(C.money) from user A,trade B ,user_money_record C where A.id=B.uid and A.id=C.uid group by A.id

不行哦 这样会有重复的计算进去的

[ACMAIN_CHM]

select *,
	(select sum(realmoney) from trade where uid=u.id),
	(select sum(money) from user_money_record where uid=u.id)
from user表 u

[lshfong]

select *,
	(select sum(realmoney) from trade where uid=u.id),
	(select sum(money) from user_money_record where uid=u.id)
from user表 u

[zjxhdlj]

引用 6 楼 ACMAIN_CHM 的回复:

select *,
	(select sum(realmoney) from trade where uid=u.id),
	(select sum(money) from user_money_record where uid=u.id)
from user表 u

还有个

select u.realname,u.money,
    (select sum(realmoney) from trade_history where uid=u.id) as totalMoney,
    (select sum(money) from user_money_record where uid=u.id) as allMoney
from user u

[zjxhdlj]

引用 6 楼 ACMAIN_CHM 的回复:

select *,
	(select sum(realmoney) from trade where uid=u.id),
	(select sum(money) from user_money_record where uid=u.id)
from user表 u

用错了 是这个

select u.name,t.realmoney,umr.money from user u
left join 
(select uid,sum(realmoney) as realmoney from trade group by uid) t on t.uid = u.id
left join
(select uid,sum(money) as money from user_money_record group by uid) umr on umr.uid = uid

[fcy_n]

user表中money代表什么？余额？

[gzcitizeny]

同意八楼的建议。

[VertigozZ]

user表建的就有问题 ，你也没说明字段和需求的对应关系

[子阙]

select sum(id) from ( select count(nr.goods_id)as id from ncp_release nr UNION ALL select count(pr.goods_id)as id from pesticide_release pr UNION ALL select count(fr.goods_id)as id from fertilizer_release fr )as b