17,377
社区成员
发帖
与我相关
我的任务
分享
--完善下
select id,col1,col2,col3,
(decode(col1,1,1,0)+decode(col2,1,1,0)+decode(col3,1,1,0)) 总数,
concat(to_char(round((decode(col1,1,1,0)+decode(col2,1,1,0)+decode(col3,1,1,0))/3,3)*100,'990.9'),'%') 百分比 from tb
select id,col1,col2,col3,
(decode(col1,1,1)+decode(col2,1,1)+decode(col3,1,1)) 总数,
concat(to_char(round((decode(col1,1,1,0)+decode(col2,1,1,0)+decode(col3,1,1,0))/3,3)*100,'990.9'),'%') 百分比 from tb
select id,col1,col2,col3 ,
col1+col2+col3 as 个数,
round((col1+col2+col3)/3,3)*100||'%' 百分比
from tablename
--第一个3是这里的所有字段为一的总和,第二个是3是三位小数,当然你也的根据你的字段数目做相应的改动
SQL> edi
已写入 file afiedt.buf
1 with tb as(
2 select 1 id,1 col1,0 col2,1 col3 from dual
3 union all
4 select 2 id,0 col1,1 col2,0 col3 from dual
5 union all
6 select 3 id,1 col1,0 col2,0 col3 from dual
7 )
8 select id,col1,col2,col3,
9 (decode(col1,1,1,0)+decode(col2,1,1,0)+decode(col3,1,1,0)) 总数,
10 concat(to_char(round((decode(col1,1,1,0)+decode(col2,1,1,0)
11 +decode(col3,1,1,0))/3,3)*100,'990.9'),'%') 百分比
12* from tb
SQL> /
ID COL1 COL2 COL3 总数 百分比
---------- ---------- ---------- ---------- ---------- -------
1 1 0 1 2 66.7%
2 0 1 0 1 33.3%
3 1 0 0 1 33.3%
col 百分比 format a20 加长长度
with temp as(
select 1 id,1 col1,0 col2,1 col3 from dual
union all
select 2 id,0 col1,1 col2,0 col3 from dual
union all
select 3 id,1 col1,0 col2,0 col3 from dual
)
select id,col1,col2,col3,subtal,to_char((subtal/sum(subtal) over())*100,999999.99)||'%' total from(
select id,col1,col2,col3,decode(col1,1,1,0)+decode(col2,1,1,0)+decode(col3,1,1,0) subtal from temp
)
----decode(col3,1,1,0))/3,3)*100,'990.9'),'%'