ORACLE如何求几列数的平均值

Question

特别注意不要出现除0错误
DECODE(DECODE(A,NULL,0,1)+DECODE(C,NULL,0,1)+DECODE(C,NULL,0,1),0,NULL,
nvl(A,0)+NVL(B,0)+NVL(C,0)/DECODE(A,NULL,0,1)+DECODE(C,NULL,0,1)+DECODE(C,NULL,0,1)
)

Answer 1

(c1+c2+c3)/decode(decode(c1,null,0,1)+decode(c2,null,0,1)+decode(c3,null,0,1),0,1,decode(c1,null,0,1)+decode(c2,null,0,1)+decode(c3,null,0,1))

Answer 2

(c1+c2+c3)/decode(decode(c1,null,0,1)+decode(c2,null,0,1)+decode(c3,null,0,1),0,1,decode(c1,null,0,1)+decode(c2,null,0,1)+decode(c3,null,0,1))
这个不行吧，只要有一个为空值，最后平均值也将是空值

Answer 3

例如你的表名是t2(a varchar2(30),b varchar2(30),c varchar2(30)),a,b,c的值是‘’，‘1’，‘3’
begin
create table t1(a varchar2(30));
insert into t1 select avg(a) from t2;
insert into t1 select avg(b) from t2;
insert into t1 select avg(c) from t2;
select avg(a) from t2;              --三列的平均值
end;
/
仅供参考