MySQL子查询分组求和时所有日期返回相同合计值问题
MySQL按月份分组统计不同modeId求和值全部相同问题修复
问题根因
你编写的SQL中子查询没有关联外层的MonthYearAutoGen分组条件,每个子查询都是对全表对应modeId的所有数据求和,所以不管外层按哪个月份分组,返回的都是全表对应modeId的总和,自然所有日期维度的统计值完全一致。
最优修复方案(条件聚合)
相比嵌套子查询,条件聚合写法执行效率更高、语法更简洁,不会出现关联遗漏问题:
SELECT MonthYearAutoGen, SUM(CASE WHEN modeId = 'HDFC 875 KM' THEN Collection ELSE 0 END) AS `HDFC 875 KM`, SUM(CASE WHEN modeId = 'synd 233 SET' THEN Collection ELSE 0 END) AS `synd 233 SET`, SUM(CASE WHEN modeId = 'Synd 217 KM' THEN Collection ELSE 0 END) AS `Synd 217 KM` FROM accounts.collectionta GROUP BY MonthYearAutoGen ORDER BY MIN(DateInput);
注意:GROUP BY分组后排序要使用聚合函数包裹
DateInput,否则MySQL开启ONLY_FULL_GROUP_BY模式时会抛出语法错误,这里用MIN(DateInput)取每个月份最早的输入日期排序,和原需求逻辑一致。
子查询写法修复方案
如果你需要保留原有子查询写法,需要给每个子查询加上和外层分组的日期关联条件:
SELECT t1.MonthYearAutoGen, (SELECT SUM(Collection) FROM accounts.collectionta t2 WHERE t2.modeId = 'HDFC 875 KM' AND t2.MonthYearAutoGen = t1.MonthYearAutoGen) AS `HDFC 875 KM`, (SELECT SUM(Collection) FROM accounts.collectionta t2 WHERE t2.modeId = 'synd 233 SET' AND t2.MonthYearAutoGen = t1.MonthYearAutoGen) AS `synd 233 SET`, (SELECT SUM(Collection) FROM accounts.collectionta t2 WHERE t2.modeId = 'Synd 217 KM' AND t2.MonthYearAutoGen = t1.MonthYearAutoGen) AS `Synd 217 KM` FROM accounts.collectionta t1 GROUP BY t1.MonthYearAutoGen ORDER BY MIN(t1.DateInput);
内容的提问来源于stack exchange,提问作者Mirza Sahaban Baig
相关产品推荐
相关产品推荐

