select 1/3 from sysibm.sysdummy1;

如果想要最初的语句1/3得到非零值。可以使用如下方法：

(1) select 1.0/3 from sysibm.sysdummy1; ---得到小数值 结果： --0.333333333333333333333333333333

(2) select 1/3.0 from sysibm.sysdummy1; ----同样得到小数值 结果： --0.3333333333333333333

(3) select cast(1 as float)/3 from sysibm.sysdummy1; --使用cast将1转为float型，然后再才除以3. 结果：0.3333333333333333

(4) select dec(1,10,2)/3 from sysibm.sysdummy1;---使用dec函数将1转换为decimal(10,2)，然后除以3 结果：0.33333333333333333333333

其实如果想要把2个数的商四舍五入保存两位小数，

oracle中可以直接使用round函数即可：

select round(a/b,2) from dual;

而db2中却要绕几个弯才行：需要使用

select dec(cast(a as float)/b+0.005,2) from sysibm.sysdummy1; 先用cast转换a为float型，然后运算，再使用+0.005作为四舍五入，然后再使用dec截取2位小数。或者：

select cast(round(cast(a as float)/b,2) as decimal(10,2)) from sysibm.sysdummy1; 先使用cast转a为float，然后运算，再使用round四舍五入取2位小数，然后使用cast转换为decimal(10,2)型。

例如：

1.Oracle数据库sql语句转换成DB2数据库sql语句

a.Oracle sql语句

select *

from (select t.device_name,

count(t.device_name) as num,

to_char(round(count(t.device_name) /

(select sum(aa)

from (select count(t.device_name) as aa

from EQUIPMENT_MONITORING t

where 1 = 1

and t.device_area = 'AA区'

group by t.device_name)) * 100,

2),

'990.99') || '%' as num_percent,

sum(trunc(t.deal_time - t.occa_time) * 24 * 60 * 60) as occur

from EQUIPMENT_MONITORING t

where 1 = 1

and t.device_area = 'AA区'

group by t.device_name) v

where v.occur <> 0;

b.DB2 sql语句

select t.device_name,

count(t.device_name) as num,

CHAR(CAST(round(cast(count(t.device_name) as float) /

(select sum(aa)

from (select count(t.device_name) as aa

from EQUIPMENT_MONITORING t

where 1 = 1

and t.device_area = 'AA区'

group by t.device_name)) * 100,

2) as decimal(3,2))) || '%' as num_percent,

sum(dec(t.deal_time - t.occa_time) * 24 * 60 * 60) as occur

from EQUIPMENT_MONITORING t

where 1 = 1

and t.device_area = 'AA区'

group by t.device_name;