SQL跨年度按年月分组时GROUP BY未区分年份问题求助

你写的SQL问题出在内层UNION连接的两个子查询的分组逻辑：两个子查询都仅按月份（MONTH(service_date)）分组，没有把年份维度加入分组规则，导致不同年份的同月份数据在子查询层就已经被提前聚合，外层查询即使写了按year+month分组，也无法拆分已经被合并的跨年数据。
另外你当前的内层查询写法本身不符合SQL标准：SELECT列表中包含了YEAR(service_date)字段，但没有将其加入GROUP BY，在开启ONLY_FULL_GROUP_BY模式的数据库中会直接报错，未开启的情况下返回的year值是不确定的，无法匹配正确的统计年份。

SELECT  
`index`,  
`month`,  
`year`,   
SUM(maintained) AS maintained,   
SUM(repaired) AS repaired  
FROM
(  
SELECT   
        MONTH(`service_date`) AS 'index',  
        MONTHNAME(`service_date`) AS 'month',  
        YEAR(`service_date`) AS 'year',  
        COUNT(`service_date`) AS 'maintained',  
        0  AS repaired  
FROM  `service_log`  
-- 内层分组补全年份维度，保证聚合粒度到「年+月」
GROUP BY YEAR(`service_date`), MONTH(`service_date`), MONTHNAME(`service_date`)   

UNION  

SELECT   
        MONTH(`service_date`) AS 'index',  
        MONTHNAME(`service_date`) AS 'month',  
        YEAR(`service_date`) AS 'year',  
        0 AS 'maintained',  
        COUNT(`other_repairs`) AS 'repaired'  
FROM  `service_log`  
-- 第二个子查询同步补全分组维度
GROUP BY YEAR(`service_date`), MONTH(`service_date`), MONTHNAME(`service_date`)  
)  
  
AS union_name  
-- 外层分组加上index字段，避免月份名重复导致分组错乱
GROUP BY `year`,`month`,`index`  
ORDER BY `year` DESC,`index`  DESC

• 聚合查询的分组维度必须在第一次聚合的层级就写全，数据提前汇总后外层无法再拆分出更细的维度
• 所有出现在SELECT列表中、未被聚合函数包裹的字段，都必须加入GROUP BY列表，避免返回不确定的结果
• 外层分组加上index（月份数字值）字段，可以避免多语言环境下月份名称重复带来的分组错误

内容的提问来源于stack exchange，提问作者Livefish