如何求每一列、每一行的平均值 。为0 的话 就不参与计算
如下：
Name num1 num2 num 3
张三 2 2 2
李四 4 4 4
王二 6 6 6
老大 0 4 12
我要得到的结果是
Name num1 num2 num 3 AVG_Y
张三 2 2 2 2
李四 4 4 4 4
王二 6 6 6 6
老大 0 4 12 8
AVG_X 4 4 6 4.7
请问以上怎么实现呢、、、、、
------解决方案--------------------
列数固定不？
------解决方案--------------------
select Name,num1,num2,num3,(num1+num2+num3)/
(case when num1=0 then 0 else 1.0 end +
case when num2=0 then 0 else 1.0 end +
case when num3=0 then 0 else 1.0 end
) as AVG_Y
from 表名
where num1+num2+num3<>0
------解决方案--------------------
create table u01
(Name varchar(10),num1 int,num2 int,num3 int)
insert into u01
select '张三',2,2,2 union all
select '李四',4,4,4 union all
select '王二',6,6,6 union all
select '老大',0,4,12
with t as
(select Name,num1,num2,num3,
(num1+num2+num3)/
(case when num1<>0 then 1 else 0 end+
case when num2<>0 then 1 else 0 end+
case when num3<>0 then 1 else 0 end) 'AVG_Y'
from u01)
select Name,num1,num2,num3,AVG_Y from t
union all
select 'AVG_X',
sum(num1)/sum(case when num1<>0 then 1 else 0 end),
sum(num2)/sum(case when num2<>0 then 1 else 0 end),
sum(num3)/sum(case when num3<>0 then 1 else 0 end),
cast((sum(num1)/sum(case when num1<>0 then 1 else 0 end)+
sum(num2)/sum(case when num2<>0 then 1 else 0 end)+
sum(num3)/sum(case when num3<>0 then 1 else 0 end))/3.0 as decimal(5,1))
from t
/*
Name num1 num2 num3 AVG_Y
---------- ----------- ----------- ----------- ---------
张三 2 2 2 2.0