select A.姓名, B.数值 from 用户表 A, ( select 姓名, sum(数值) as 数值 from ( select 姓名, 0-sum(数值) as 数值 from 扣费表 union all select 姓名, sum(数值) from 充值表 ) t group by 姓名 ) B where A.姓名 = B.姓名
--如果要反应所有的用户select a.姓名,差额=sum(b.数值) from 用户表 a left join( select 姓名,数值 from 充值表 union all select 姓名,-数值 from 扣费表 )a on a.姓名=b.姓名 group by a.姓名
select a.姓名 (b.充值和-c.扣费和) from 用户表 a, (select id ,sum(充值值) from 充值表 group by id) b, (select id ,sum(扣费值) from 扣费表 group by id) c, where a.id = b.id and a.id = c.id
select A.姓名, isnull(B.数值,0) as 数值 from 用户表 A left join ( select 姓名, sum(数值) as 数值 from ( select 姓名, 0-sum(数值) as 数值 from 扣费表 union all select 姓名, sum(数值) from 充值表 ) t group by 姓名 ) B on A.姓名 = B.姓名
select 姓名,(充值-扣费) as 差额 from 用户表 table1, (select xm,sum(数值)as 扣费 from 扣费表 group by xm)table2, (select xm,sum(数值)as 充值 from 充值表 group by xm)table3 where table1 left join out table2 on table1.xm=table2.xm left join out table3 on table1.xm=table3.xm
用户表 A,
(
select 姓名, sum(数值) as 数值 from
(
select 姓名, 0-sum(数值) as 数值 from 扣费表
union all
select 姓名, sum(数值) from 充值表
) t group by 姓名
) B where A.姓名 = B.姓名
from 用户表 a left join(
select 姓名,数值 from 充值表
union all
select 姓名,-数值 from 扣费表
)a on a.姓名=b.姓名 group by a.姓名
from 用户表 a,
(select id ,sum(充值值) from 充值表 group by id) b,
(select id ,sum(扣费值) from 扣费表 group by id) c,
where a.id = b.id and a.id = c.id
select A.姓名, isnull(B.数值,0) as 数值 from
用户表 A left join
(
select 姓名, sum(数值) as 数值 from
(
select 姓名, 0-sum(数值) as 数值 from 扣费表
union all
select 姓名, sum(数值) from 充值表
) t group by 姓名
) B on A.姓名 = B.姓名
from 用户表 table1,
(select xm,sum(数值)as 扣费 from 扣费表 group by xm)table2,
(select xm,sum(数值)as 充值 from 充值表 group by xm)table3
where table1 left join out table2 on table1.xm=table2.xm left join out table3 on table1.xm=table3.xm