34,838
社区成员




--> 测试数据: #T
if object_id('tempdb.dbo.#T') is not null drop table #T
create table #T (Year int,Month int,Number numeric(2,1))
insert into #T
select 2008,5,4.0 union all
select 2008,6,2.0 union all
select 2008,7,5.0 union all
select 2008,8,3.0 union all
select 2008,9,1.0 union all
select 2008,10,2.0 union all
select 2008,11,4.0 union all
select 2008,12,4.0
insert #T select Year+1,Month,Number from #T
if object_id('tempdb.dbo.#') is not null drop table #
select top 12 Month=identity(int,1,1) into # from syscolumns
-->某年
declare @Year int
set @Year=2008
select Year=@Year,a.Month,Number=isnull(b.Number,0) from # a left join #T b on a.Month=b.Month and b.Year=@Year
/*
Year Month Number
----------- ----------- -----------
2008 1 0.0
2008 2 0.0
2008 3 0.0
2008 4 0.0
2008 5 4.0
2008 6 2.0
2008 7 5.0
2008 8 3.0
2008 9 1.0
2008 10 2.0
2008 11 4.0
2008 12 4.0
*/
-->全部
select a.Year,a.Month,Number=isnull(b.Number,0) from
(
select * from (select distinct Year from #T) t cross join #
) a
left join #T b
on a.Year=b.Year and a.Month=b.Month
/*
Year Month Number
----------- ----------- -----------
2008 1 0.0
2008 2 0.0
2008 3 0.0
2008 4 0.0
2008 5 4.0
2008 6 2.0
2008 7 5.0
2008 8 3.0
2008 9 1.0
2008 10 2.0
2008 11 4.0
2008 12 4.0
2009 1 0.0
2009 2 0.0
2009 3 0.0
2009 4 0.0
2009 5 4.0
2009 6 2.0
2009 7 5.0
2009 8 3.0
2009 9 1.0
2009 10 2.0
2009 11 4.0
2009 12 4.0
*/
select m.* , isnull(n.number,0) number from
(
select 2008 [Year] , 1 [month] union
select 2008 [Year] , 2 [month] union
select 2008 [Year] , 3 [month] union
select 2008 [Year] , 4 [month] union
select 2008 [Year] , 5 [month] union
select 2008 [Year] , 6 [month] union
select 2008 [Year] , 7 [month] union
select 2008 [Year] , 8 [month] union
select 2008 [Year] , 9 [month] union
select 2008 [Year] , 10 [month] union
select 2008 [Year] , 11 [month] union
select 2008 [Year] , 12 [month]
) m
left join tb n
on m.[Year] = n.[Year] and m.[month] = n.[month]
select m.* , isnull(n.number,0) number from
(
select 2008 [Year] , 1 [month] union
select 2008 [Year] , 2 [month] union
select 2008 [Year] , 3 [month] union
select 2008 [Year] , 4 [month] union
select 2008 [Year] , 5 [month] union
select 2008 [Year] , 6 [month] union
select 2008 [Year] , 7 [month] union
select 2008 [Year] , 8 [month] union
select 2008 [Year] , 9 [month] union
select 2008 [Year] , 10 [month] union
select 2008 [Year] , 11 [month] union
select 2008 [Year] , 12 [month]
) m
left join tb n
on m.[Year] = n.[Year] and m.[month] = n.[month]