                        课程
                    
                        /数据库
                        
                            /MySQL
                        
                        /与MySQL的零距离接触

查询所有分类 group by 报错 this is incompatible with sql _mode=only_full_group_by

语句:mysql> SELECT p.type_id,p.type_name,count(s.type_name) AS children_coun F

ROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p

.type_id GROUP BY p.type_name ORDER BY p.type_id;

错误:ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause

and contains nonaggregated column 'test.p.type_id' which is not functiona

lly dependent on columns in GROUP BY clause; this is incompatible with sql

_mode=only_full_group_by

油金哇卡呀酷咧

2016-09-05

源自：与MySQL的零距离接触 5-13

关注问题我要回答

2804

操作

收起

2 回答

虫虫妞回答被采纳 +3 积分
2016-09-05

ORDER BY中的列，应该再GROUP BY子句中出现，应该这样写：GROUP BY p.type_name,p.type_id ORDER BY p.type_id;

0 回复有任何疑惑可以回复我~

收起回答

油金哇卡呀酷咧提问者

非常感谢！

2016-09-05 回复有任何疑惑可以回复我~

油金哇卡呀酷咧提问者
2016-09-05

我查到这个错误出现,group by 后的字段要与 select 后的字段相同,

如果想要只写一个字段就要加 set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

可以查看mysql5.1参考手册 5.3.2 SQL服务器模式一节

出处http://www.imooc.com/qadetail/162552

2 回复有任何疑惑可以回复我~

收起回答

scool_wang

问题解决了！

2016-10-27 回复有任何疑惑可以回复我~