create table B(id int, uid varchar(10), sj datetime,je decimal(18,2))
insert into b values(1 ,'张三', '2012-02-01', 200.00)
insert into b values(2 ,'李四', '2012-02-01', 100.00)
insert into b values(4 ,'张三', '2012-02-15', 100.00)
create table C(id int, uid varchar(10), sj datetime,je decimal(18,2))
insert into c values(1 ,'张三', '2012-02-02', 200.00)
insert into c values(2 ,'赵六', '2012-02-05', 100.00)
insert into c values(3 ,'陈七', '2012-02-17', 50.00)
go
select isnull(m.uid,n.uid) uid ,
isnull(m.je , 0) 充值金额,
isnull(n.je , 0) 消费金额,
isnull(m.je , 0) - isnull(n.je , 0) 余额
from
(select uid , sum(je) je from b where convert(varchar(10),sj,120) between '2012-02-01' and '2012-02-15' group by uid) m
full join
(select uid , sum(je) je from c where convert(varchar(10),sj,120) between '2012-02-01' and '2012-02-15' group by uid) n
on m.uid = n.uid
drop table b , c
/*
uid 充值金额 消费金额 余额
---------- ---------------------------------------- ---------------------------------------- ----------------------------------------
李四 100.00 .00 100.00
张三 300.00 200.00 100.00
赵六 .00 100.00 -100.00
（所影响的行数为 3 行）
*/
