方便更快捷的说明问题,可以按需填写(可删除)
使用环境:
springboot + shardingSphere5.0 + mybatis + mysql
场景、问题:
表:informations
,informations_2021
,informations_2022
。
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
运行结果
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
运行结果
最终实际接口返回的结果: “score”: “75”
但正确的结果就是数据库运行结果:150。
目前系统中存在很多这种包含子查询的sql,请问如何解决分表计算结果有误的问题?