34,838
社区成员




create table tb(字段1 int, 字段2 int, 字段3 int, 字段4 int, 字段5 int)
insert tb select 1, 2, 3, NUll, NULL
union all select 1,2,3,4,5
union all select null,null,null,null,null
select case (isnull(字段1,0)+isnull(字段2,0)+isnull(字段3,0)+isnull(字段4,0)+isnull(字段5,0))
when 0 then 0
else (isnull(字段1,0)+isnull(字段2,0)+isnull(字段3,0)+isnull(字段4,0)+isnull(字段5,0))
/(isnumeric(字段1)+isnumeric(字段2)+isnumeric(字段3)+isnumeric(字段4)+isnumeric(字段5))
end as average
from tb
/*
average
-----------
2
3
0
(3 row(s) affected)
*/
drop table tb
--try
create table tb(字段1 int, 字段2 int, 字段3 int, 字段4 int, 字段5 int)
insert tb select 1, 2, 3, NUll, NULL
union all select 1,2,3,4,5
union all select null,null,null,null,null
select case (isnull(字段1,0)+isnull(字段2,0)+isnull(字段3,0)+isnull(字段4,0)+isnull(字段5,0))
when 0 then 0
else (isnull(字段1,0)+isnull(字段2,0)+isnull(字段3,0)+isnull(字段4,0)+isnull(字段5,0))
/(5-isnull(nullif(isnull(字段1,1), 字段1), 0)-isnull(nullif(isnull(字段2,1), 字段2), 0)
-isnull(nullif(isnull(字段3,1), 字段3), 0)-isnull(nullif(isnull(字段4,1), 字段4), 0)
-isnull(nullif(isnull(字段5,1), 字段5), 0))
end as average
from tb
/*
average
-----------
2
3
0
(3 row(s) affected)
*/
drop table tb
create table tb(字段1 int, 字段2 int, 字段3 int , 字段4 int , 字段5 int)
insert into tb values( 1 , 2 , 3 , NUll, NULL )
select myavg =
(isnull(字段1,0) + isnull(字段2,0) + isnull(字段3,0) + isnull(字段4,0) + isnull(字段5,0))/
(case when 字段1 is not null then 1 else 0 end +
case when 字段2 is not null then 1 else 0 end +
case when 字段3 is not null then 1 else 0 end +
case when 字段4 is not null then 1 else 0 end +
case when 字段5 is not null then 1 else 0 end)
from tb
drop table tb
/*
myavg
-----------
2
(所影响的行数为 1 行)
*/
declare @tb table (字段1 int,字段2 int,字段3 int,字段4 int,字段5 int)
insert into @tb select 1,2,3,null,null
select
sum(isnull(字段1,0)+isnull(字段2,0)+isnull(字段3,0)+isnull(字段4,0)+isnull(字段5,0))/
sum(case when 字段1 is null then 0 else 1 end +
case when 字段2 is null then 0 else 1 end +
case when 字段3 is null then 0 else 1 end +
case when 字段4 is null then 0 else 1 end +
case when 字段5 is null then 0 else 1 end ) as '平均数'
from @tb