select convert(varchar(10),日期,120) as 日期,sum(消费金额) as 消费金额 from 消费明细表 group by convert(varchar(10),日期,120)
select 日期,sum(消费金额) as 日消费金额 from YouTable where datepart(yyyy,日期)='2003' group by 日期 order by 日期
select a.帐号,日期=dateadd(day,b.id,'2003-1-1') ,消费金额=isnull(sum(a.消费金额),0) from 消费明细表 a right join( select id=a.id+b.id+c.id from( select id=0 union all select 1 union all select id=2 union all select 3 union all select id=4 union all select 5 union all select id=6 union all select 7 union all select id=8 union all select 9 ) a,( select id=0 union all select 10 union all select id=20 union all select 30 union all select id=40 union all select 50 union all select id=60 union all select 70 union all select id=80 union all select 90 ) b,( select id=0 union all select 100 union all select id=200 union all select 300 )c where a.id+b.id+c.id<365 )b on datediff(day,'2003-1-1',a.日期)=b.id group by a.帐号,dateadd(day,b.id,'2003-1-1')
--如果累计是指,2号的包含1,2号的,3号的包含1,2,3号的,则改为:select *,消费金额=isnull(( select sum(消费金额) from 消费明细表 where 帐号=a.帐号 and datediff(day,a.日期,日期)>=0 and year(日期)=2003),0) from( select a.帐号,日期=dateadd(day,b.id,'2003-1-1') from 消费明细表 a right join( select id=a.id+b.id+c.id from( select id=0 union all select 1 union all select id=2 union all select 3 union all select id=4 union all select 5 union all select id=6 union all select 7 union all select id=8 union all select 9 ) a,( select id=0 union all select 10 union all select id=20 union all select 30 union all select id=40 union all select 50 union all select id=60 union all select 70 union all select id=80 union all select 90 ) b,( select id=0 union all select 100 union all select id=200 union all select 300 )c where a.id+b.id+c.id<365 )b on datediff(day,'2003-1-1',a.日期)=b.id group by a.帐号,dateadd(day,b.id,'2003-1-1') )a
where datepart(yyyy,日期)='2003' group by 日期
order by 日期
,消费金额=isnull(sum(a.消费金额),0)
from 消费明细表 a right join(
select id=a.id+b.id+c.id
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b,(
select id=0 union all select 100
union all select id=200 union all select 300
)c
where a.id+b.id+c.id<365
)b on datediff(day,'2003-1-1',a.日期)=b.id
group by a.帐号,dateadd(day,b.id,'2003-1-1')
select sum(消费金额) from 消费明细表
where 帐号=a.帐号 and datediff(day,a.日期,日期)>=0
and year(日期)=2003),0)
from(
select a.帐号,日期=dateadd(day,b.id,'2003-1-1')
from 消费明细表 a right join(
select id=a.id+b.id+c.id
from(
select id=0 union all select 1
union all select id=2 union all select 3
union all select id=4 union all select 5
union all select id=6 union all select 7
union all select id=8 union all select 9
) a,(
select id=0 union all select 10
union all select id=20 union all select 30
union all select id=40 union all select 50
union all select id=60 union all select 70
union all select id=80 union all select 90
) b,(
select id=0 union all select 100
union all select id=200 union all select 300
)c
where a.id+b.id+c.id<365
)b on datediff(day,'2003-1-1',a.日期)=b.id
group by a.帐号,dateadd(day,b.id,'2003-1-1')
)a
小弟等待你们的回答!谢谢!