有A,B两个字段,需要对A求平均值,但是有两个要求 1。A为0的值不参与平均 2。当B为0时,要将A的值根据公式(如A=A-1)转换后用转换的值进行平均计算select avg(A) A from ( select case B when 0 then A - 1 else A end A from tb where A <> 0 ) t
SQL> create table t5(a int,b int);Table createdSQL> insert into t5 select 0,10 from dual 2 union select 8,9 from dual 3 union select 9,0 from dual 4 union select 6,8 from dual;4 rows insertedSQL> select avg( 2 case when b=0 then a-1 else a end 3 ) tol from t5 where a<>0; TOL ---------- 7.33333333
SELECT AVG(A) FROM (SELECT DECODE(B,0,A - 1,A) A,B FROM 表名 WHERE A <> 0) TAB;
1。A为0的值不参与平均
2。当B为0时,要将A的值根据公式(如A=A-1)转换后用转换的值进行平均计算select avg(A) A from
(
select case B when 0 then A - 1 else A end A from tb where A <> 0
) t
SQL> create table t5(a int,b int);Table createdSQL> insert into t5 select 0,10 from dual
2 union select 8,9 from dual
3 union select 9,0 from dual
4 union select 6,8 from dual;4 rows insertedSQL> select avg(
2 case when b=0 then a-1 else a end
3 ) tol from t5 where a<>0; TOL
----------
7.33333333