MariaDB多statistical_id IN查询仅返回最小ID结果的问题排查
异常原因分析
- 核心错误是GROUP BY子句与SELECT非聚合字段不匹配:
你需要按每个statistical_id、每个统计日期独立聚合,但原语句的GROUP BY只写了date(created_at),既没有加statistical_id分组,也没有对齐SELECT里经过时区转换的日期表达式。MariaDB在未开启ONLY_FULL_GROUP_BY严格模式时,同组内的非聚合列会默认取任意值,刚好你这批数据里最小的statistical_id是3133,就被固定选中输出,其他ID的数据全被合并进了同一个日期分组里,所以你看不到其他ID的结果,返回的异常结果里所有行的water_type都是0也是同个原因导致的。 - 附带逻辑问题:
- SELECT中额外查询的
water_type列没有实际业务意义:你的计算逻辑是把0/2/3三类计量值统一换算为净水量,最终输出不需要区分原始水类型,加了这个列反而会进一步引发分组歧义。 - 日期口径不统一:SELECT里的日期是经过
CONVERT_TZ转换时区后的结果,而GROUP BY用的是原始created_at的日期,两者统计口径不一致,即使补上statistical_id分组,跨时区日期的数据也会统计错误。 - 时间过滤范围不匹配:你给出的WHERE时间范围是
2020-10-04 22:00:00到2020-10-15 22:00:00,但样例数据的created_at都在10月24-26日,按原时间条件过滤所有样例数据都不会被返回,大概率是你举例时写错了时间范围。
- SELECT中额外查询的
正确查询写法
需要把所有非聚合的SELECT字段都纳入GROUP BY,去掉多余的water_type列,统一日期统计口径:
SELECT statistical_id AS device_id, DATE(CONVERT_TZ(created_at, '+00:00', '+02:00')) AS `date`, SUM( CASE WHEN water_type = 0 THEN water ELSE - water END ) AS water FROM `production`.statistics WHERE statistical_id IN (3144, 3133, 3145) -- 替换为你实际需要的时间范围,若要按转换后时区过滤,需和分组逻辑保持一致 AND created_at >= '2020-10-24 00:00:00' AND created_at <= '2020-10-27 00:00:00' AND period = 1 AND water_type IN (0, 2, 3) -- 所有非聚合字段必须和SELECT列完全对齐 GROUP BY statistical_id, `date`;
注意事项
- 可通过
SELECT @@sql_mode;查看当前数据库配置,建议开启ONLY_FULL_GROUP_BY模式,此类分组不匹配的语句会直接报错,避免隐式的随机取值问题。 - 若确实需要按原始
created_at的日期分组,需把SELECT里的日期表达式也改为DATE(created_at),保持口径统一。
内容的提问来源于stack exchange,提问作者useless9276
相关产品推荐
相关产品推荐

