解决mysql sum求和返回null问题或IFNULL应用

问题描述：sum求和要求返回float（或 integer或double等）类型，但当数据库不存在任何符合求和记录时，sum返回null，报类型绑定错误异常（mybatis：

org.apache.ibatis.binding.BindingException: Mapper method 'com.danaaa.cm.dao.AgentWithdrawalMapper.getTotalByWeek attempted to return null from a method with a primitive return type (float).
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:69)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:43)
at com.sun.proxy.$Proxy222.getTotalByWeek(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:207)
at com.sun.proxy.$Proxy223.getTotalByWeek(Unknown Source)

）

解决办法：IFNULL(SUM(transfer_amount),0)，当数据库不存在任何符合求和记录时，sum返回0

例如：SELECT IFNULL(SUM(transfer_amount),0) FROM cm_agent_withdrawal WHERE transfer_status in (1,2,3)

mysql IFNULL(expr1,expr2)

如果 expr1 不是 NULL，IFNULL() 返回 expr1，否则它返回 expr2。

IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。

更多相关文章

随机推荐