关于round(to_number())结果取舍说明
gbase> select (2.85/ (to_days(last_day('2016-02-28'))-to_days('2016-02-28')+1) )*to_number(substr(last_day('2016-02-28'),9,2));
+------------------------------------------------------------------------------------------------------------------+
| (2.85/ (to_days(last_day('2016-02-28'))-to_days('2016-02-28')+1) )*to_number(substr(last_day('2016-02-28'),9,2)) |
+------------------------------------------------------------------------------------------------------------------+
| 41.325 |
+------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
把它带到round里，
select round( (2.85/ (to_days(last_day('2016-02-28'))-to_days('2016-02-28')+1) )*to_number(substr(last_day('2016-02-28'),9,2)),2);
+----------------------------------------------------------------------------------------------------------------------------+
| round( (2.85/ (to_days(last_day('2016-02-28'))-to_days('2016-02-28')+1) )*to_number(substr(last_day('2016-02-28'),9,2)),2) |
+----------------------------------------------------------------------------------------------------------------------------+
| 41.32 |
+----------------------------------------------------------------------------------------------------------------------------+
结果不是四舍五入得来。
回答 (2)
简化用例
gbase> select round(41.325,2),round(to_number('41.325'),2);
+-----------------+------------------------------+
| round(41.325,2) | round(to_number('41.325'),2) |
+-----------------+------------------------------+
| 41.33 | 41.32 |
+-----------------+------------------------------+
1 row in set (Elapsed: 00:00:00.00)
经分析，GBase中的to_number函数将数值字符串转化成了double类型数值，而DOUBLE代表一个浮点型数值，它所存储的数值不是一个准确值。所以造成了结果的差别。
可以通过转化数值字符为精确数值类型解决，如下：
gbase> select round(41.325,2),round(cast('41.325' as decimal(16,5)),2);
+-----------------+------------------------------------------+
| round(41.325,2) | round(cast('41.325' as decimal(16,5)),2) |
+-----------------+------------------------------------------+
| 41.33 | 41.33 |
+-----------------+------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)
补充：
对double类型作round，采取了“四舍六入五凑偶”的规则。
即“四舍”是指≤4 时舍去，
"六入"是指≥6时进上，
"五凑偶"指的是根据5后面的数字来定，当5后有数时，舍5入1；
当5后无数或为0时，需要分两种情况来讲：
①5前为奇数，舍5入1；
②5前为偶数，舍5不进。