oracle查询 求百分比

[wattor]

Oracle数据库中，一个表有两个字段，所有数量（allnum），成功数量（sucessnum），都是long类型的
select allnum,sucessnum from mytabl;

现在想在查询处增加一列：成功率（用百分比，小数点保留两位数字）

select allnum,sucessnum,sucessnum/allnum from mytabl; 这样不能实现我想要的效果

[bw555]

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

[csuxp2008]

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

试试看

[csuxp2008]

上面的错了，更正一下：

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

[hyrongg]

SQL> select * from t3;

    ALLNUM  SUCESSNUM
---------- ----------
        88         43
        53         20
        57         23
        76         21

SQL> 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> 

[wattor]

楼上的太细致了，佩服

[wjwwj33]

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

[csuxp2008]

方法一：select allnum,sucessnum,to_char((sucessnum/allnum)*100,'99D99')||'%' from mytabl;
方法二：select allnum,SUCESSNUM,round((SUCESSNUM/allnum)*100,2)||'%' 成功率 from mytabl;

两种方法均可，我已测试过

[cab1225]

SQL> select * from t3;

ALLNUM SUCESSNUM
---------- ----------
88 43
53 20
57 23
76 21

SQL> 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 21

SQL> 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 21

SQL> 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>

[a89723253]

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

[Richard345265669]

RoundZ就可以了

[jun1100]

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

这个貌似有点问题.