MySQL数据库遇到的问题（一）

╰半橙微兮° 2022-05-11 16:54 263阅读 0赞

使用GROUP\_CONCAT()和并数据，并且使用GROUP BY 进行分组时，报错要查询的字段不在聚合函数内？  
![在这里插入图片描述][70]

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  
显示为：  
![在这里插入图片描述][70 1]  
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][https_dev.mysql.com_doc_refman_5.7_en_miscellaneous-functions.html_function_any-value]

![在这里插入图片描述][70 2]

[70]: /images/20220504/0700273ecd2240d2b1b5a07f6caf04be.png
[70 1]: /images/20220504/215c16d35d5c4c5b90f0f6b8005c1c19.png
[https_dev.mysql.com_doc_refman_5.7_en_miscellaneous-functions.html_function_any-value]: https://dev.mysql.com/doc/refman/5.7/en/miscellaneous-functions.html#function_any-value
[70 2]: /images/20220504/e057c33bf2bd4e118ad1e6d143395ecc.png