springboot + shardingSphere5.0 + mybatis + mysql 使用分表的AVG,导致结果不正确。

方便更快捷的说明问题,可以按需填写(可删除)

使用环境:

springboot + shardingSphere5.0 + mybatis + mysql


场景、问题:

表:informations
image

,informations_2021
image
,informations_2022
image

logic sql :

SELECT
AVG(a.score) score
FROM
(SELECT
i.name,
SUM(score) score
FROM
informations i
WHERE i.time BETWEEN ‘2021-12-30’
AND ‘2022-01-01’
AND i.name IN (‘aa’, ‘bb’)
GROUP BY i.name) a

数据库运行结果:150。

Actual sql:

SELECT
AVG(a.score) score,
COUNT(a.score) AS AVG_DERIVED_COUNT_0,
SUM(a.score) AS AVG_DERIVED_SUM_0
FROM
(SELECT
i.name,
SUM(score) score
FROM
informations_2021 i
WHERE i.time BETWEEN ‘2021-12-30’
AND ‘2022-01-01’
AND i.name IN (‘aa’, ‘bb’)
GROUP BY i.name) a

运行结果image

SELECT
AVG(a.score) score,
COUNT(a.score) AS AVG_DERIVED_COUNT_0,
SUM(a.score) AS AVG_DERIVED_SUM_0
FROM
(SELECT
i.name,
SUM(score) score
FROM
informations_2022 i
WHERE i.time BETWEEN ‘2021-12-30’
AND ‘2022-01-01’
AND i.name IN (‘aa’, ‘bb’)
GROUP BY i.name) a

运行结果image

最终实际接口返回的结果: “score”: “75”

但正确的结果就是数据库运行结果:150。

目前系统中存在很多这种包含子查询的sql,请问如何解决分表计算结果有误的问题?

@用户1 感谢反馈,我看下这个问题

京ICP备2021015875号