关于统计月份数据的问题，在没有的数据上面添加一条为0的数据

[tdhao]

有一个表用来统计数据，比如说按年份查询，
表里面有1-12个月的记录，里面有可能有些月份没有记录
需要查询整个1-12月中出没有记录的让它数据为0

比如数据库中如下记录：
Year Month Number
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
这里没有1-4月份的记录，需要给它加上去变成这样
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

[dawugui]

test

[dawugui]

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] 

[dawugui]

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] 

[Limpire]

--> 测试数据: #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
*/

[gggg007]

怎么看不到回复

[gggg007]

怎么看不到回复