DB2 小数点处理

以保留两位小数为例，代码如下：

select
	--floor
	cast((0.333*1000 - mod(0.333*1000, 10)) / 10 / 100 as decimal(20,2))		as f333,
	cast((0.335*1000 - mod(0.335*1000, 10)) / 10 / 100 as decimal(20,2))		as f335,
	cast((0.330*1000 - mod(0.330*1000, 10)) / 10 / 100 as decimal(20,2))		as f330,
	--round
	cast(((0.333*1000+5) - mod((0.333*1000+5), 10)) / 10 / 100 as decimal(20,2))	as r333,
	cast(((0.335*1000+5) - mod((0.335*1000+5), 10)) / 10 / 100 as decimal(20,2))	as r335,
	cast(((0.330*1000+5) - mod((0.330*1000+5), 10)) / 10 / 100 as decimal(20,2))	as r330,
	--ceil
	case mod((0.333*1000), 10) when 0 then cast(0.333 as decimal(20,2)) else cast(((0.333*1000+10) - mod((0.33*1000+10), 10)) / 10 / 100 as decimal(20,2)) end	as c333,
	case mod((0.335*1000), 10) when 0 then cast(0.335 as decimal(20,2)) else cast(((0.335*1000+10) - mod((0.33*1000+10), 10)) / 10 / 100 as decimal(20,2)) end	as c335,
	case mod((0.330*1000), 10) when 0 then cast(0.330 as decimal(20,2)) else cast(((0.330*1000+10) - mod((0.33*1000+10), 10)) / 10 / 100 as decimal(20,2)) end	as c330
from
	sysibm.sysdummy1
with ur

执行结果如下：

另一种写法：

select
	test_value,
	--floor
	cast((test_value*1000 - mod(test_value*1000, 10)) / 10 / 100 as decimal(20,2))			as floor_value,
	--round
	cast(((test_value*1000+5) - mod((test_value*1000+5), 10)) / 10 / 100 as decimal(20,2))	as round_value,
	--ceil
	case mod((test_value*1000), 10) when 0 then
		cast(test_value as decimal(20,2))
	else
		cast(((test_value*1000+10) - mod((test_value*1000+10), 10)) / 10 / 100 as decimal(20,2))
	end																						as ceil_value
from
	(
		select test_value from table(values(0.330),(0.333),(0.335)) as TEMP(test_value)
	)
with ur

执行结果如下：

其实不光是DB2，其它语言也可以这么做，比如JS，直接用floor会造成精度不准确。用这种方法就没有精度不准确的问题。