关于统计月份数据的问题,在没有的数据上面添加一条为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
...全文
96 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧