查詢所有分類 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
ORDER BY中的列,應該再GROUP BY子句中出現,應該這樣寫:GROUP BY p.type_name,p.type_id ORDER BY p.type_id;
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.xianlaiwan.cn/qadetail/162552