有一个表用来统计数据,比如说按年份查询,
表里面有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
表里面有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
(
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 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]
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.0insert #T select Year+1,Month,Number from #Tif 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
*/