--测试数据
declare @T table(yd varchar(10),rs int)
insert @T
select '2006-1',5 union all
select '2006-2',8 union all
select '2006-5',10 union all
select '2006-8',3 union all
select '2006-12',10
select top 12 mon=identity(int,1,1) into #mon from syscolumns
select yd=a.yd+ltrim(b.mon),rs=isnull(c.rs,0) from
(select distinct yd=left(yd,5) from @T) a
cross join #mon b
left join @T c
on a.yd+ltrim(b.mon)=c.yd
/*
yd rs
2006-1 5
2006-2 8
2006-3 0
2006-4 0
2006-5 10
2006-6 0
2006-7 0
2006-8 3
2006-9 0
2006-10 0
2006-11 0
2006-12 10
*/
--删除测试
drop table #mon
declare @T table(yd varchar(10),rs int)
insert @T
select '2006-1',5 union all
select '2006-2',8 union all
select '2006-5',10 union all
select '2006-8',3 union all
select '2006-12',10
select top 12 mon=identity(int,1,1) into #mon from syscolumns
select yd=a.yd+ltrim(b.mon),rs=isnull(c.rs,0) from
(select distinct yd=left(yd,5) from @T) a
cross join #mon b
left join @T c
on a.yd+ltrim(b.mon)=c.yd
/*
yd rs
2006-1 5
2006-2 8
2006-3 0
2006-4 0
2006-5 10
2006-6 0
2006-7 0
2006-8 3
2006-9 0
2006-10 0
2006-11 0
2006-12 10
*/
--删除测试
drop table #mon