目录
一、背景
最近开发遇到大量的数据计算,开发过程中发现,有的逻辑使用的是Java实现,有的逻辑使用的是SQL实现,同一个计算公式,计算结果不同,会差0.01(如果保留两位的话),究其原因就在于是否四舍五入、向上取整还是向下取整。
二、SQL版计算公式总结
1、ROUND(X)与ROUND(X,D)
四舍五入取证截取。如果D不传递,默认为0,D可以为负数,这时指定小数点左边D位整数之后所有数据为0
示例:SELECT ROUND(12345.6789); 结果为:12346
SELECT ROUND(12345.6789,2); 结果为:12345.68
SELECT ROUND(12345.6789,-2); 结果为:12300
2、FLOOR(X)
向下取整截取。
示例:SELECT FLOOR(12345.6789); 结果为:12345
3、CEILING(X)
向上取整截取
示例:SELECT CEILING(12345.6789); 结果为:12346
4、TRUNCATE(X,D)(不进行四舍五入)
D表示:保留小数点之后D位,不进行四舍五入。D可以为负数,这时指定小数点左边D位整数取整(不进行四舍五入)
示例:SELECT TRUNCATE(12365.6789,2); 结果为:12365.67
SELECT TRUNCATE(12365.6789,-2); 结果为:12300
5、FORMAT(X,D)
强制保留D为小数,超过部分以逗号分割,返回结果为String类型。D同样可以为负数,四舍五入计算。
示例: SELECT FORMAT(123456789.6789,2); 结果为:123,456,789.68
SELECT FORMAT(123456789.6789,-2); 结果为:123,456,790
SELECT FORMAT(123456789.6789,5); 结果为:123,456,789.67890
6、CONVERT(expr,type)
类型转化,进行截取,支持四舍五入。
1) DECIMAL - 将值转化为浮点类型。格式DECIMAL(X,D)
2) DATE - 将值转换为DATE。格式:"YYYY-MM-DD"
3) DATETIME - 将值转换为DATETIME。格式:"YYYY-MM-DD HH:MM:SS"
4) TIME - 将值转换为TIME。格式:"HH:MM:SS"
5) CHAR - 将值转换为CHAR(固定长度的字符串)
6) SIGNED - 将值转换为SIGNED(带符号的64位整数)
7) UNSIGNED - 将值转换为UNSIGNED(无符号的64位整数)
8) BINARY - 将值转换为BINARY(二进制字符串)
示例:
SELECT 20*100/103; 结果为:19.4175
SELECT CONVERT((20/103)*100,DECIMAL(10,2)); 结果为:19.42
SELECT CONVERT("2021-11-05", DATE); 结果为:2021-11-05
SELECT CONVERT("2021-11-05 14:06:10", DATETIME); 结果为:2021-11-05 14:06:10
SELECT CONVERT("14:06:10", TIME); 结果为:14:06:10
SELECT CONVERT(150.12345, CHAR); 结果为:150.12345
SELECT CONVERT((20/103)*100,SIGNED); 结果为:19
SELECT CONVERT((20/103)*100,UNSIGNED); 结果为:19
SELECT CONVERT((20/103)*100,BINARY); 结果为:19.4175
三、Java版计算公式总结
使用BigDecimal 工具类进行计算
1、示例代码:
BigDecimal first = new BigDecimal(2);
BigDecimal second = new BigDecimal("11");
BigDecimal third = new BigDecimal("3");
System.out.println("a+b="+first.add(second));
System.out.println("a-b="+first.subtract(second));
System.out.println("a*b="+first.multiply(second));
// System.out.println("a/b="+first.divide(second));//报错!
System.out.println("a/b="+first.divide(second,2,BigDecimal.ROUND_CEILING));
System.out.println("a/b="+first.divide(second,2,BigDecimal.ROUND_DOWN));
System.out.println("a/b="+first.divide(second,2,BigDecimal.ROUND_FLOOR));
System.out.println("a/b="+first.divide(second,2,BigDecimal.ROUND_HALF_DOWN));
System.out.println("a/b="+first.divide(second,2,BigDecimal.ROUND_HALF_EVEN));
// System.out.println("a/b="+first.divide(second,2,BigDecimal.ROUND_UNNECESSARY));//报错
System.out.println("a/b="+first.divide(second,2,BigDecimal.ROUND_UP));
2、构造器描述
BigDecimal(int) 创建一个具有参数所指定整数值的对象。
BigDecimal(double) 创建一个具有参数所指定双精度值的对象。 //不推荐使用
BigDecimal(long) 创建一个具有参数所指定长整数值的对象。
BigDecimal(String) 创建一个具有参数所指定以字符串表示的数值的对象。//推荐使用
3、方法描述
add(BigDecimal) BigDecimal对象中的值相加,然后返回这个对象。
subtract(BigDecimal) BigDecimal对象中的值相减,然后返回这个对象。
multiply(BigDecimal) BigDecimal对象中的值相乘,然后返回这个对象。
divide(BigDecimal) BigDecimal对象中的值相除,然后返回这个对象。
toString() 将BigDecimal对象的数值转换成字符串。
doubleValue() 将BigDecimal对象中的值以双精度数返回。
floatValue() 将BigDecimal对象中的值以单精度数返回。
longValue() 将BigDecimal对象中的值以长整数返回。
intValue() 将BigDecimal对象中的值以整数返回。
5、舍入模式
ROUND_CEILING //向正无穷方向舍入
ROUND_DOWN //向零方向舍入
ROUND_FLOOR //向负无穷方向舍入
ROUND_HALF_DOWN //向(距离)最近的一边舍入,除非两边(的距离)是相等,如果是这样,向下舍入, 例如1.55 保留一位小数结果为1.5
ROUND_HALF_EVEN //向(距离)最近的一边舍入,除非两边(的距离)是相等,如果是这样,如果保留位数是奇数,使用ROUND_HALF_UP,如果是偶数,使用ROUND_HALF_DOWN
ROUND_HALF_UP //向(距离)最近的一边舍入,除非两边(的距离)是相等,如果是这样,向上舍入, 1.55保留一位小数结果为1.6,也就是我们常说的“四舍五入”
ROUND_UNNECESSARY //计算结果是精确的,不需要舍入模式
ROUND_UP //向远离0的方向舍入
四、参考文献
mysql怎么四舍五入取整_mysql-向上,向下,四舍五入-取整函数_谢丽鹿的博客-CSDN博客
MySql中保留两位小数_青菜12131的博客-CSDN博客_mysql保留两位小数的函数
mysql计算结果取整_Sql 获取向上取整、向下取整、四舍五入取整的实例(转)_weixin_39521068的博客-CSDN博客
https://www.nhooo.com/sql/func-mysql-convert.html