select sum(to_number(col1)) as col1,avg(to_number(replace(col2,'%',''))||'%' as col2 from xxx
SQL> select * from test;COL1 COL2 ---------- ---------- 2 10% 2 10% 3 10% 4 10% 9 20%SQL> select sum(case when instrb(col1,'%')=0 then col1 end) sum_col1, 2 to_char(avg(case when instrb(col2,'%')>0 then replace(col2,'%','') end))||'%' avg_col2 3 from test; SUM_COL1 AVG_COL2 ---------- ----------------------------------------- 20 12%SQL>
select sum(to_number(col1)) as col1,avg(to_number(replace(col2,'%',''))||'%' as col2 from xxx
col1,col2,col3 是数值还是百分比不确定的情况下可以这样create table ttt as select * from ( select 1 as id,'2' as col1,'10%' as col2 from dual union select 2 as id,'2' as col1,'10%' as col2 from dual union select 3 as id,'3' as col1,'10%' as col2 from dual union select 4 as id,'4' as col1,'10%' as col2 from dual union select 5 as id,'9' as col1,'20%' as col2 from dual );select * from ttt;
select c1 / (case when instr((select col1 from ttt where rownum = 1), '%') = 0 then 1 else (select count(1) from ttt) end) || (case when instr((select col1 from ttt where rownum = 1), '%') = 0 then '' else '%' end) as col1, c2 / (case when instr((select col2 from ttt where rownum = 1), '%') = 0 then 1 else (select count(1) from ttt) end) || (case when instr((select col2 from ttt where rownum = 1), '%') = 0 then '' else '%' end) as col2 from (select sum(replace(col1, '%', '')) as c1, sum(replace(col2, '%', '')) as c2 from ttt)查询结果如下 col1 col2 20 12%
---------- ----------
2 10%
2 10%
3 10%
4 10%
9 20%SQL> select sum(case when instrb(col1,'%')=0 then col1 end) sum_col1,
2 to_char(avg(case when instrb(col2,'%')>0 then replace(col2,'%','') end))||'%' avg_col2
3 from test; SUM_COL1 AVG_COL2
---------- -----------------------------------------
20 12%SQL>
select * from (
select 1 as id,'2' as col1,'10%' as col2 from dual union
select 2 as id,'2' as col1,'10%' as col2 from dual union
select 3 as id,'3' as col1,'10%' as col2 from dual union
select 4 as id,'4' as col1,'10%' as col2 from dual union
select 5 as id,'9' as col1,'20%' as col2 from dual );select * from ttt;
select c1 / (case
when instr((select col1 from ttt where rownum = 1), '%') = 0 then
1
else
(select count(1) from ttt)
end) || (case
when instr((select col1 from ttt where rownum = 1), '%') = 0 then
''
else
'%'
end) as col1,
c2 / (case
when instr((select col2 from ttt where rownum = 1), '%') = 0 then
1
else
(select count(1) from ttt)
end) || (case
when instr((select col2 from ttt where rownum = 1), '%') = 0 then
''
else
'%'
end) as col2
from (select sum(replace(col1, '%', '')) as c1,
sum(replace(col2, '%', '')) as c2
from ttt)查询结果如下
col1 col2
20 12%