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

来源：互联网发布：mac下显示隐藏文件夹编辑：程序博客网时间：2024/05/23 19:55

--返回年份表函数

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;