类型：转载 责任编辑：asp.net 日期：2007/05/23
热门软件下载：
表1
客户 日期 购买金额
A 2004-1-1 1500
A 2004-2-1 2500
A 2004-4-1 2000
表2
客户 日期 支付金额
A 2004-5-1 1500
A 2004-6-1 2000
A 2004-7-1 1000
求客户A的余额
网友回答：
select 余额=(select sum(购买金额) from 表1 where 客户=A)
-(select sum(支付金额) from 表2 where 客户=A)
declare @a int,@b int
select @a=sum(购买金额) from 表1
select @b=sum(购买金额) from 表2
select @b-@a
select 客户,(购买金额 - 支付金额) as余额
from
(select 客户,购买金额,0 as 支付金额 from 表1 group by 客户
union
select 客户, 0 as 购买金额,支付金额 from 表2group by 客户)
select ((select sum(支付金额) as 支付总额 from 表2 where 客户=A and 日期<convert(nvarchar(10),getdate(),120))-(select sum(购买金额) as 购买总额 from 表1 where 客户=A and 日期<convert(nvarchar(10),getdate(),120))) as 余额
这样不行吗？
select (select sum( 支付金额) from 表2)-(select sum(购买金额) from 表1) 余额
select 客户,总购买金额=sum(购买金额),总支付金额=sum(支付金额),余额=sum(余额)
from(
select 客户,购买金额,余额=购买金额 from 表1
union all
select 客户,支付金额,-支付金额 from 表2
)a group by 客户
select a.客户,sum(a.购买金额) as 总购买金额,sum(b.支付金额) as 总支付金额,
sum(a.购买金额)-sum(b.支付金额) as 余额
from 表1 a inner join 表2 b on a.客户=b.客户
group by a.客户