- 如下SQL执行过程中会报错
SELECT pid,exams_id,exams_name FROM FROM GROUP BY pid ;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
and contains nonaggregated column 'xx' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible
with sql_mode=only_full_group_by
- 在MySQL关闭ONLY_FULL_GROUP_BY之后可以正常执行。
show variables like '%sql_mode%' ;
SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
show variables like '%sql_mode%' ;
- 同样的功能在MySQL可以通过any_value也可以解决
SELECT pid,ANY_VALUE(exams_id),ANY_VALUE(exams_name) FROM FROM GROUP BY pid ;
- 在Doirs可以通过如下SQL达到需要的效果.
SELECT pid,max(exams_id),max(exams_name) FROM FROM GROUP BY pid ;
因为ONLY_FULL_GROUP_BY关闭后,MySQL可以支持一些不严谨的SQL语法。可以用any_value或者max、min替换实现。
- 同时Doris支持了any_value函数,可以通过any_value来实现
mysql> select id, any_value(name) from cost2 group by id;
+------+-------------------+
| id | any_value(`name`) |
+------+-------------------+
| 3 | jack |
| 2 | jack |
+------+-------------------+
详细可以参考:https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
最后宣传下我的书:
1 . 《图解Spark 大数据快速分析实战(异步图书出品)》 https://item.jd.com/13613302.html
2. 《Offer来了:Java面试核心知识点精讲(第2版)(博文视点出品)》https://item.jd.com/13200939.html
3. 《Offer来了:Java面试核心知识点精讲(原理篇)(博文视点出品)》https://item.jd.com/12737278.html
4. 《Offer来了:Java面试核心知识点精讲(框架篇)(博文视点出品) https://item.jd.com/12868220.html