MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2

 

描述：

2021-08-xx 13:31:30.049 DEBUG 9208 : ==>  Preparing: SELECT SUM(end_vt) - SUM(start_vt) FROM s_dc_volume where ADDITION = 0 and START_TIME >= ? and END_TIME <= ? 
2021-08-xx 13:31:30.050 DEBUG 9208 : ==> Parameters: 2021-06-01 20:57:00.0(Timestamp), 2021-08-29 19:37:00.0(Timestamp)
2021-08-xx 13:31:30.201 DEBUG 9208 : <==      Total: 2

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.TooManyResultsException: Expected one result (or null) to be returned by selectOne(), but found: 2

    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:78)
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
    at com.sun.proxy.$Proxy92.selectOne(Unknown Source)
    at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159)
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:89)

 

分析：

1.项目使用了sharding对s_dc_volume进行分表，查询范围涉及到多个子表

2.mapper方法接收直接使用数字类型，未使用list

BigDecimal getSumVolume(Date startTime, Date endTime);

    <select id="getSumVolume" resultType="java.math.BigDecimal">
        SELECT SUM(end_vt) - SUM(start_vt)
        FROM s_dc_volume
        where and ADDITION = 0
          and START_TIME <![CDATA[ >=]]> #{startTime}
          and END_TIME <![CDATA[ <=]]> #{endTime}
    </select>

 

结论：

sharding使用sum求和时具体到每个子表中求和，对于跨表查询时，会返回多个结果

 

sharding官网：

https://shardingsphere.apache.org/document/current/cn/features/sharding/use-norms/sql/