使用GROUP_CONCAT()和并数据,并且使用GROUP BY 进行分组时,报错要查询的字段不在聚合函数内?
SQL语句为:
SELECT
oh.order_code as order_code,
oh.goods_text,
oh.origin_desc,
oh.destination_desc,
oh.gmt_date,
oh.modified_date,
oh.carrier_enterprise_name AS carrierEnterpriseName,
oh.shipper_enterprise_name AS shipperEnterpriseName,
wb.order_header_id,
wb.zy_code,
wb.loading_weight,
wb.loading_date,
wb.unloading_weight,
wb.unloading_date,
wb.driver_name,
wb.lossing_weight,
wb.status,
GROUP_CONCAT(od.product_type) AS product_type
FROM order_header AS oh JOIN waybill AS wb ON oh.id=wb.order_header_id
JOIN order_detail od ON oh.id = od.order_header_id
WHERE oh.shipper_enterprise_id='91330400579304109X'
GROUP BY oh.id
报错信息:
1055 - Expression #10 of SELECT list is not in GROUP BY clause and contains nonaggregated column ‘qr.wb.zy_code’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
第一种解决办法(不推荐):
1.查看 SELECT @@sql_mode
显示为:
sql_mode = “STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
找到数据库配置文件并修改上述信息为:
“STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”
然后重启数据库,OK 解决了。
第二种解决办法:
1.在报错信息显示的字段前加上 ANY_VALUE( ),OK,完美解决,参考MySQL官网:https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value