a 表有字段 id,moneysum id为主键
b 表有字段 id,carry_money
c 表有字段 id, inpay_money
我的sql 大概是这样 select a.id,a.moneysum,sum(b.carry_money),sum(c.inpay_money) from a left join b on a.id=b.id left join c on a.id=c.id group by a.id,a.moneysum 这条语句该怎么改下 。出现的问题是C表里面的inpay_money会累加一次, 谁帮忙改一下
b 表有字段 id,carry_money
c 表有字段 id, inpay_money
我的sql 大概是这样 select a.id,a.moneysum,sum(b.carry_money),sum(c.inpay_money) from a left join b on a.id=b.id left join c on a.id=c.id group by a.id,a.moneysum 这条语句该怎么改下 。出现的问题是C表里面的inpay_money会累加一次, 谁帮忙改一下
a.id,
a.moneysum,
b.carry_money,
c.inpay_money
from a
left join (SELECT ID,SUM(carry_money) AS carry_money FROM b GROUP BY ID) AS b on a.id=b.id
left join (SELECT ID,SUM(inpay_money) AS inpay_money FROM c GROUP BY ID) AS c on a.id=c.id
a表 id moneysum
1 2
b表 id carry_sum
1 2
1 2
c表 id inpay_sum
1 2
2 3
查询出来的结果是 1 2 4 4
而正确的结果应该是 1 2 4 2
a.id,
a.moneysum,
ISNULL(b.carry_money,0) AS carry_money,
ISNULL(c.inpay_money,0) AS inpay_money
from a
left join (SELECT ID,SUM(carry_money) AS carry_money FROM b GROUP BY ID) AS b on a.id=b.id
left join (SELECT ID,SUM(inpay_money) AS inpay_money FROM c GROUP BY ID) AS c on a.id=c.id