平均数的问题 ，求一个SQL
原始表TB如下：
ID A(int) B(int) C(int)
1 10 20 30
2 10 null 20
3 10 null null
4 0 null 10
得到如下的表,平均数=总分/N，当为null的值 不计算到 求平均数的N 中
比如ID为2的B 值为空，计算平均的时候就是 （10+20）/2 而不是除3
ID A(int) B(int) C(int) 总分 平均分
1 10 20 30 60 20
2 10 null 20 30 15
3 10 null null 10 10
4 10 null 10 20 10
求一个SQL 函数，存储过程 都可以
[解决办法]
--创建建测试环境
create table #tb(ID int,A int,B int ,C int)
--插入测试数据
insert #tb(ID,A,B,C)
select '1 ', '10 ', '20 ', '30 ' union all
select '2 ', '10 ',null, '20 ' union all
select '3 ', '10 ',null,null union all
select '4 ', '0 ',null, '10 '
--求解过程
select *,isnull(a,0)+isnull(b,0)+isnull(c,0) as 总分
,(isnull(a,0)+isnull(b,0)+isnull(c,0))/(case when a is null then 0 else 1 end +case when b is null then 0 else 1 end +case when c is null then 0 else 1 end) as 平均
from #tb
--删除测试环境
drop table #tb
/*--测试结果
ID A B C 总分 平均
----------- ----------- ----------- ----------- ----------- -----------
1 10 20 30 60 20
2 10 NULL 20 30 15
3 10 NULL NULL 10 10
4 0 NULL 10 10 5
（所影响的行数为 4 行）
*/
[解决办法]
select *, (isnull(a,0)+isnull(b,0)+isnull(c,0))/(case when a is null then 0 else 1 end+
case when b is null then 0 else 1 end+case when c is null then 0 else 1 end) as avg
from tb
[解决办法]
create table T(ID int, A int, B int, C int)
insert T select 1, 10, 20, 30
union all select 2, 10, null, 20
union all select 3, 10, null, null
union all select 4, 10, null, 10
select *,
总分=isnull(A, 0)+isnull(B, 0)+isnull(C, 0),
平均分=(isnull(A, 0)+isnull(B, 0)+isnull(C, 0))/
(case when A is null then 0 else 1 end+case when B is null then 0 else 1 end+case when C is null then 0 else 1 end)
from T
[解决办法]
create table #tb(ID int,A int,B int ,C int)
insert #tb(ID,A,B,C)
select '1 ', '10 ', '20 ', '30 ' union all
select '2 ', '10 ',null, '20 ' union all
select '3 ', '10 ',null,null union all
select '4 ', '0 ',null, '10 '
select * ,总分=(isnull(a,0)+isnull(b,0)+isnull(c,0)),
平均分=((isnull(a,0)+isnull(b,0)+isnull(c,0))/((case when a is not null then 1 else 0 end)+
(case when b is not null then 1 else 0 end)+(case when c is not null then 1 else 0 end)))
from #tb
ID A B C 总分 平均分
----------- ----------- ----------- ----------- ----------- -----------
1 10 20 30 60 20
2 10 NULL 20 30 15
3 10 NULL NULL 10 10
4 0 NULL 10 10 5
（所影响的行数为 4 行）
drop table #tb
[解决办法]
一句搞定
select case when sum(a) is null or sum(a)=0 then 0 else
sum(a)/sum(case when a is null then 0 else 1 end) end as a,
case when sum(b) is null or sum(b)=0 then 0 else
sum(b)/sum(case when b is null then 0 else 1 end) end as b,
case when sum(c) is null or sum(c)=0 then 0 else
sum(c)/sum(case when c is null then 0 else 1 end) end as c
from tb
