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

关于统计月份数据的问题,在没有的数据上面添加一条为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]
回复
dawugui 2008-03-28
test
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-28 12:13
社区公告
暂无公告