MySQL 5.0视图迁移至Oracle 12c时GROUP BY报错及行数差异求助

这个问题的核心是MySQL和Oracle对GROUP BY子句的语法规则差异：

• MySQL在旧版默认配置下（未开启ONLY_FULL_GROUP_BY），允许SELECT列表中出现不在GROUP BY里的非聚合列，只要这些列在逻辑上依赖于分组列（比如通过关联关系唯一确定）。
• Oracle则严格遵循ANSI SQL标准，要求SELECT中的所有非聚合列必须要么出现在GROUP BY子句中，要么在逻辑上是分组列的函数依赖（比如分组列是关联表的主键，对应的其他列由主键唯一确定）。

你的原始MySQL查询中，table3.name虽然不在GROUP BY里，但因为table3.uuid = table1.courtuuid，且table3.uuid大概率是table3的主键（否则MySQL的结果其实是随机取的一个name），所以MySQL允许这种写法。但Oracle默认不会自动识别这种函数依赖，因此抛出了ORA-00979错误。

两种可行的解决方案 #

方案1：利用Oracle的函数依赖优化（推荐） #

如果table3.uuid是table3的主键（或有唯一约束），那么table3.name完全由table3.uuid确定，而table3.uuid又等于分组列table1.courtuuid。此时可以通过修改会话参数让Oracle自动识别这种依赖：

执行查询前先运行以下语句：

ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '12.1.0.2';
ALTER SESSION SET _optimizer_group_by_placement = TRUE;

然后执行你最初改写的Oracle查询即可：

select count(table1.uuid) AS Count, 
       table2.description AS STATUS, 
       table3.NAME as Court 
from table2 
join table1 on (table1.statuscode = table2.code) 
join table3 on (table3.uuid = table1.courtuuid) 
group by table1.courtuuid, table2.description;

这个参数配置会让Oracle 12c及以上版本自动识别函数依赖，允许SELECT列表中的非分组列依赖于分组列，查询行数会和MySQL的1025行一致。

方案2：用聚合函数包裹table3.name #

如果不想修改会话参数，因为同一个table1.courtuuid对应的table3.name应该是唯一的（否则MySQL的结果本身就存在随机性），可以用MAX()或MIN()聚合函数包裹table3.name，这样不需要把它加入GROUP BY，同时结果和MySQL一致：

select count(table1.uuid) AS Count, 
       table2.description AS STATUS, 
       MAX(table3.NAME) as Court  -- 用MIN()结果也完全相同
from table2 
join table1 on (table1.statuscode = table2.code) 
join table3 on (table3.uuid = table1.courtuuid) 
group by table1.courtuuid, table2.description;

这个方案兼容性更好，不需要调整任何数据库参数，且能保证返回行数与MySQL一致。

为什么加table3.name到GROUP BY会导致行数变多？ #

如果存在同一个table1.courtuuid + table2.description的组合下，对应多个table3.name（可能是数据异常或table3.uuid不是主键导致），分组时会把不同的name拆分成独立的组，从而导致行数增加。而MySQL的原始查询其实是随机取了其中一个name，Oracle不允许这种模糊的写法，因此必须明确处理。

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