year m1 m2 m3 m4 m5 m6 m7 m8 m9 m10 m11 m12
2011 12 13 null null null 20 18 14 15 23 22 20
2011 null null null null null 21 16 16 21 18 17 19
2011 14 25 null null null 26 21 15 26 20 28 29
2011 12 13 null null null 28 19 12 18 23 16 31
2012 11 21 null null null null null null null null null null
2012 19 23 null null null null null null null null null null
2012 22 28 null null null null null null null null null null
结果：
year m1 m2 m3 m4 m5 m6 m7 m8 m9 m10 m11 m12
2011 平均值 平均值 null null null 平均值 平均值 平均值 平均值 平均值 平均值
2012 平均值 平均值 null null null null null null null null null null
------解决方案--------------------
- SQL code
select year,avg(isnull(m1,0)), avg(isnull(m2,0)), avg(isnull(m3,0)), avg(isnull(m4,0)), avg(isnull(m5,0)), avg(isnull(m6,0)), avg(isnull(m7,0)), avg(isnull(m8,0)), avg(isnull(m9,0)), avg(isnull(m10,0)),avg(isnull(m11,0)),avg(isnull(m12,0))from tabgroup by YEAR
------解决方案--------------------
- SQL code
--0为分母的时候需要判断一下declare @T table ([year] int,m1 int,m2 int,m3 int,m4 int,m5 int,m6 int,m7 int,m8 int,m9 int,m10 int,m11 int,m12 int)insert into @Tselect 2011,12,13,12,6,7,20,18,14,15,23,22,20 union allselect 2011,null,null,2,null,null,21,16,16,21,18,17,19 union allselect 2011,14,25,null,5,3,26,21,15,26,20,28,29 union allselect 2011,12,13,null,4,null,28,19,12,18,23,16,31 union allselect 2012,11,21,4,5,6,7,8,9,10,12,14,15 union allselect 2012,19,23,null,null,5,null,6,null,null,null,7,null union allselect 2012,22,28,5,6,null,null,null,null,null,null,null,nullselect [year], m1=cast(sum(isnull(m1,0))*1./count(m1) as decimal(18,2)), m2=cast(sum(isnull(m2,0))*1./count(m2)as decimal(18,2)), m3=cast(sum(isnull(m3,0))*1./count(m3)as decimal(18,2)), m4=cast(sum(isnull(m4,0))*1./count(m4)as decimal(18,2)), m5=cast(sum(isnull(m5,0))*1./count(m5)as decimal(18,2)), m6=cast(sum(isnull(m6,0))*1./count(m6)as decimal(18,2)), m7=cast(sum(isnull(m7,0))*1./count(m7)as decimal(18,2)), m8=cast(sum(isnull(m8,0))*1./count(m8)as decimal(18,2)), m9=cast(sum(isnull(m9,0))*1./count(m9)as decimal(18,2)), m10=cast(sum(isnull(m10,0))*1./count(m10)as decimal(18,2)), m11=cast(sum(isnull(m11,0))*1./count(m11)as decimal(18,2)), m12=cast(sum(isnull(m12,0))*1./count(m12)as decimal(18,2))from @t group by [year]