SQLite内置的round函数,有时候其反应和我们预期的反应不一样,粗略说来,就是double的精度问题,无法精确的表达所有实数,只是在近似的表达着它们。
下面就是round函数抽风的例子和替代的解决方案:
sqlite> select 5.75*0.9, round(5.75*0.9, 2), FLOOR( 5.75*100*0.9+0.5)/100.0;
+----------+--------------------+--------------------------------+
| 5.75*0.9 | round(5.75*0.9, 2) | FLOOR( 5.75*100*0.9+0.5)/100.0 |
+----------+--------------------+--------------------------------+
| 5.175 | 5.17 | 5.18 |
+----------+--------------------+--------------------------------+
1 row in set
sqlite> select 10.25*1.1, ROUND(10.25*1.1, 2), FLOOR(10.25*100*1.1+0.5)/100.0;
+-----------+---------------------+--------------------------------+
| 10.25*1.1 | ROUND(10.25*1.1, 2) | FLOOR(10.25*100*1.1+0.5)/100.0 |
+-----------+---------------------+---------------