MySQL中WITH ROLLUP搭配ORDER BY执行报错1221问题咨询

报错原因说明 #

该报错和开启ONLY_FULL_GROUP_BY没有直接关联，错误码1221的核心触发逻辑是MySQL 5.7后续小版本、8.0.12及之后版本对WITH ROLLUP的语法限制：默认禁止同层级的GROUP BY ... WITH ROLLUP和ORDER BY直接联用，你开启ONLY_FULL_GROUP_BY的操作刚好和该限制的触发时间重合，并非报错诱因。

无需子查询的解决方案 #

方法1：GROUP BY字段直接指定排序规则 #

不需要额外写ORDER BY语句，直接在GROUP BY的对应字段后加排序方向即可，语法兼容所有支持ROLLUP的MySQL版本：

SELECT year, person, SUM(amount) AS total_amount FROM rentals
GROUP BY year DESC, person WITH ROLLUP;

方法2：高版本MySQL兼容写法 #

如果你使用的是MySQL 8.0.20及以上版本，可以直接在ROLLUP后使用ORDER BY，补充NULLS FIRST/LAST处理ROLLUP生成的空值汇总行即可避免报错：

SELECT year, person, SUM(amount) AS total_amount FROM rentals
GROUP BY year, person WITH ROLLUP
ORDER BY year DESC NULLS LAST;

方法3：临时调整会话规则 #

如果需要兼容旧的写法不改SQL结构，可以在执行查询前先运行一次会话级配置，当前连接生效不会影响全局设置：

SET SESSION optimizer_switch='block_nested_loop=off';

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