oracle查询 求百分比

Question

Oracle数据库中，一个表有两个字段，所有数量（allnum），成功数量（sucessnum），都是long类型的
select allnum,sucessnum from mytabl;现在想在查询处增加一列：成功率（用百分比，小数点保留两位数字）select allnum,sucessnum,sucessnum/allnum from mytabl;   这样不能实现我想要的效果

Answer 1

select allnum,sucessnum,
round(sucessnum*100/allnum,2)||'%' as 成功率  
from mytabl;

Answer 2

select allnum,sucessnum,to_char(sucessnum/allnum,'99D99')||'%' from mytabl;试试看

Answer 3

上面的错了，更正一下：select allnum,sucessnum,to_char((sucessnum/allnum)*100,'99D99')||'%' from mytabl;

Answer 4

SQL> select * from t3;    ALLNUM  SUCESSNUM
---------- ----------
        88         43
        53         20
        57         23
        76         21SQL> select allnum,SUCESSNUM,round((SUCESSNUM/allnum)*100,2)||'%' percent from t3;    ALLNUM  SUCESSNUM PERCENT
---------- ---------- -----------------------------------------
        88         43 48.86%
        53         20 37.74%
        57         23 40.35%
        76         21 27.63%SQL> 

Answer 5

select allnum,sucessnum,to_char(100 * sucessnum/allnum, 'fm99999990.00') || '%' from mytabl

Answer 6

方法一：select allnum,sucessnum,to_char((sucessnum/allnum)*100,'99D99')||'%' from mytabl;
方法二：select allnum,SUCESSNUM,round((SUCESSNUM/allnum)*100,2)||'%' 成功率 from mytabl;两种方法均可，我已测试过

Answer 7

SQL> select * from t3;    ALLNUM  SUCESSNUM
---------- ----------
        88         43
        53         20
        57         23
        76         21SQL> select allnum,SUCESSNUM,round((SUCESSNUM/allnum)*100,2)||'%' percent from t3;    ALLNUM  SUCESSNUM PERCENT
---------- ---------- -----------------------------------------
        88         43 48.86%
        53         20 37.74%
        57         23 40.35%
        76         21 27.63%SQL> SQL> select * from t3;    ALLNUM  SUCESSNUM
---------- ----------
        88         43
        53         20
        57         23
        76         21SQL> select allnum,SUCESSNUM,round((SUCESSNUM/allnum)*100,2)||'%' percent from t3;    ALLNUM  SUCESSNUM PERCENT
---------- ---------- -----------------------------------------
        88         43 48.86%
        53         20 37.74%
        57         23 40.35%
        76         21 27.63%SQL> SQL> select * from t3;    ALLNUM  SUCESSNUM
---------- ----------
        88         43
        53         20
        57         23
        76         21SQL> select allnum,SUCESSNUM,round((SUCESSNUM/allnum)*100,2)||'%' percent from t3;    ALLNUM  SUCESSNUM PERCENT
---------- ---------- -----------------------------------------
        88         43 48.86%
        53         20 37.74%
        57         23 40.35%
        76         21 27.63%SQL> 

Answer 8

我同意四楼的，但在正常情况下allnum不会为0，如果非正常情况呢，所以我做了一下改动
select allnum,SUCESSNUM,decode(allnum 0,0,round((SUCESSNUM/allnum)*100,2)||'%') percent from t3;