表tableA 中的字段 有: id moneyA 表tableB中的字段有: id tableA_Id moneyBtableB通过tableA_Id 是外键关联tableA,一对一关系 ,但是tableA中有记录200条 ,tableB中只有记录130条(都在tableA中找得到tableA_Id )
请问: 现在要统计:total=(tableB中的moneyB之和 + id不存在于tableB的tableA_Id的moneyA之和),求total的值
请问: 现在要统计:total=(tableB中的moneyB之和 + id不存在于tableB的tableA_Id的moneyA之和),求total的值
id,sum(money) as [total]
from
(
select id ,moneya as [money] from tableA
union
select tableA_Id as id ,moneyb as [money] as [money] from tableA)t
group by
id
id,sum(money) as [total]
from
(
select id ,moneyb as [money] from tableB
union
select id,moneya from tableA where id not in(select id from tableB)
)t
group by
id
试下。楼上的修正了下。
为null的值会自动换算成0计算吗?
id,sum(money) as [total]
from
(
select id ,isnull(moneyb,0) as [money] from tableB
union
select id,isnull(moneya,0) from tableA where id not in(select id from tableB)
)t
group by
id
select sum(total) as [total]
from
(
select id=1 ,sum(moneyb) total from tableB
union
select id=0,sum(moneya) total from tableA where not exists(select id from tableB where tableB.id=tableA.id)
)t