全部
基础类
应用实例
新技术前沿

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

tdhao 2008-03-28 12:13:34

有一个表用来统计数据，比如说按年份查询，
表里面有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

...全文

36 点赞收藏 6

写回复

6 条回复

切换为时间正序

当前发帖距今超过3年，不再开放新的回复

发表回复

gggg007 2008-03-28

怎么看不到回复

回复赞

gggg007 2008-03-28

怎么看不到回复

回复赞

Limpire 2008-03-28

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

*/

回复赞

dawugui 2008-03-28

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 2008-03-28

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]