请大家帮忙解决一个问题

[wei123456]

现有如下两个表：
表一、ChargeMonay_Records
ChargeTime ID ChargeMoney
2004-01-02 10:10:10 C0001 20.0
2004-01-02 20:30:50 C0001 20.0
2004-05-11 17:50:22 C0001 10.0
2004-06-02 10:10:10 C0002 20.0
2004-08-11 17:50:22 C0001 10.0

表二、ConsumeMoney_Records
ConsumeTime ID ConsumeMoney
2004-01-02 10:10:10 C0001 20.0
2004-01-02 20:30:50 C0001 20.0
2004-05-13 17:50:22 C0002 10.0

怎样实现如下结果：
Time ID ChargeMoney ConsumeMoney
2004-01-02 C0001 40.0 40.0
2004-05-11 C0001 10.0 0.0
2004-05-13 C0002 0.0 10.0
2004-06-02 C0002 20.0 0.0
2004-08-11 C0001 10.0 0.0

(本人第一次发贴，请大家多多帮忙，谢谢，谢谢！)

[WangZWang]

Select Convert(datetime,a.ChargeTime,varchar(10)) as Time,a.ID,
IsNULL(sum(a.ChargeMoney),0) as ChargeMoney,
IsNULL(sum(b.ConsumeMoney),0) as ConsumeMoney
from ChargeMonay_Records as a Left Join
ConsumeMoney_Records as b
on Convert(datetime,a.ChargeTime,varchar(10))=
Convert(datetime,b.ConsumeTime,varchar(10))
and a.ID=b.ID
group by Convert(datetime,a.ChargeTime,varchar(10)) as Time,a.ID

[Andy__Huang]

select time=isnull(a.ChargeTime,b.ConsumeTime),id=isnull(a.id,b.id),ChargeMoney=sum(a.ChargeMoney),ConsumeMoney=sum(b.ConsumeMoney)
from ChargeMonay_Records a full join ConsumeMoney_Records b
on a.ChargeTime=b.ConsumeTime and a.id=b.id
group by isnull(a.ChargeTime,b.ConsumeTime),isnull(a.id,b.id)


你試一下

[lsxaa]

select isnull(convert(varchar,a.time,120),convert(varchar,b.time,120)) as time,
isnull(a.id,b.id) as id,
isnull(a.ChargeMoney,0.0) as ChargeMoney,
isnull(b.ConsumeMoney,0.0) as ChargeMoney
from ChargeMonay_Records a full join ConsumeMoney_Records b
on convert(varchar,a.time,120)=convert(varchar,b.time,120)
and a.id=b.id

[WangZWang]

改一下试:
Select Convert(varchar(10),a.ChargeTime,120) as Time,a.ID,
IsNULL(sum(a.ChargeMoney),0) as ChargeMoney,
IsNULL(sum(b.ConsumeMoney),0) as ConsumeMoney
from ChargeMonay_Records as a Left Join
ConsumeMoney_Records as b
on Convert(varchar(10),a.ChargeTime,120)
Convert(varchar(10),a.ChargeTime,120)
and a.ID=b.ID
group by Convert(varchar(10),a.ChargeTime,120) as Time,a.ID

[wei123456]

谢谢大家帮忙，“WangZWang(阿来)”你的语句怎么试都有一点错误，“hdhai9451”你的语句还差一点，“lsxaa(小李铅笔刀) ”真厉害！