这个是我在一个小项目里查询时间范围内的经费总入账/总出账/总存款遇到的
double XXX(@Param("statrtTime") String statrtTime,@Param("endTime")String endTime,@Param("kind")String kind);
<select id="XXX" resultType="Double">
SELECT SUM(cost) FROM XXX where state=3
<if test="statrtTime!=null and statrtTime!='' and endTime!=null and endTime!=''">
AND DATE_FORMAT(success_time,'%Y-%m-%d') BETWEEN #{statrtTime} AND #{endTime}
</if>
<if test="kind!=null and kind!=''">
<if test="kind == '1'.toString()">
AND cost > 0
</if>
<if test="kind == '-1'.toString()">
AND cost < 0
</if>
</if>
</select>
double XXX(@Param("statrtTime") String statrtTime,@Param("endTime")String endTime,@Param("kind")String kind);
<select id="XXX" resultType="Double">
SELECT IFNULL(SUM(cost), 0) FROM XXX where state=3
<if test="statrtTime!=null and statrtTime!='' and endTime!=null and endTime!=''">
AND DATE_FORMAT(success_time,'%Y-%m-%d') BETWEEN #{statrtTime} AND #{endTime}
</if>
<if test="kind!=null and kind!=''">
<if test="kind == '1'.toString()">
AND cost > 0
</if>
<if test="kind == '-1'.toString()">
AND cost < 0
</if>
</if>
</select>
问题出现原因和解决方法
实际上只是给sql语句中的SUM(cost)
改为IFNULL(SUM(cost), 0)
,这个错误是因为在查询的时候如果指定范围内的查询到的结果为null时回到值数据封装到double失败,我们只需要在sql语句中设定,如果返回为null就返回默认值即可,而IFNULL(XXX, 0)
函数就有这个效果
本文地址:https://blog.csdn.net/P_ning/article/details/107633254
如对本文有疑问, 点击进行留言回复!!
HBase Filter 过滤器之FamilyFilter详解
去 HBase,Kylin on Parquet 性能表现如何?
如何找到Hive提交的SQL相对应的Yarn程序的applicationId
网友评论