在统计每月信息的时候,有时候该月没有信息,但是还是显示出来

 --原贴地址http://topic.youkuaiyun.com/u/20090510/13/e48161d0-f100-4151-8cfc-0895869706bb.html?seed=1668282403

create table #BUY_IN
(
  [date] datetime,
  [money] int,
)
insert into #BUY_IN select '2009-1-1',1000
insert into #BUY_IN select '2009-2-1',2000
insert into #BUY_IN select '2009-5-1',500

--说下思路吧!先根据查询的日期,得到年月信息到一个临时表
declare @StartTime datetime
declare @EndTime datetime
set @StartTime='2009-1-1'
set @EndTime='2009-5-10'
declare @i int
set @i=0
create table #11
(
  b int,
  a int
)
while (dateadd(month,@i,@StartTime)<=@EndTime)
begin
  insert into #11 select year(@StartTime),month(@StartTime)+@i
  set @i=@i+1
end
select * from #11

select t.b '年',isnull(t.a,0) '月份',isnull(sum(B.[money]),0)'总金额' from #BUY_IN B right join #11 t
on t.a=month(B.[date]) group by t.a,t.b
年           月份          总金额
----------- ----------- -----------
2009        1           1000
2009        2           2000
2009        3           0
2009        4           0
2009        5           500