[MSSQL2005]月统计SQL脚本（包括没有记录的月份）

--返回年份表函数

create function dbo.getyears
()
returns @years table
(
y varchar(2)
)
as
begin
 insert into @years values('01');
 insert into @years values('02');
 insert into @years values('03');
 insert into @years values('04');
 insert into @years values('05');
 insert into @years values('06');
 insert into @years values('07');
 insert into @years values('08');
 insert into @years values('09');
 insert into @years values('10');
 insert into @years values('11');
 insert into @years values('12');
 return;
end
go

--测试脚本

--临时测试用表
declare @temp table (y datetime,num int);

insert into @temp values('2009-08-10',100);
insert into @temp values('2009-09-10',200);
insert into @temp values('2009-10-10',300);
insert into @temp values('2009-10-10',400);

 

--统计脚本

select e.y,isnull(sum(t.num),0)
 from dbo.getyears() e
 left join @temp t on e.y=substring(convert(varchar(7),t.y,21),6,2)
 group by e.y;

 

--substring(convert(varchar(7),t.y,21) 返回当前日期月份（月份不足两位时补零）

 

drop function dbo.getyears;