Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'kaixi_homecare.a.id'

时间: 2023-08-17 12:13:48 浏览: 91

解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated

主要介绍了解决大于5.7版本mysql的分组报错Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated问题,需要的朋友可以参考下在MySQL 5.7.5及更高版本中，数据库系统引入了更为严格的SQL模式`ONLY_FULL_GROUP_BY`，这是为了遵循SQL标准，确保查询结果的一致性和可预测性。在这个模式下，当执行包含`GROUP BY`语句的查询时，如果`SELECT`列表中的列没有出现在`GROUP BY`子句中，且该列没有被聚合函数（如`SUM`, `AVG`, `MAX`, `MIN`等）处理，MySQL会抛出`Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated`错误。这个问题在5.7版本之前并不严格检查，但在默认设置下，它成为了强制性的检查。 **问题背景与原因：** 在SQL标准中，`GROUP BY`语句用于将数据分组，而`SELECT`列表中的每个项目要么是分组列，要么需要与分组列一起使用聚合函数。当`ONLY_FULL_GROUP_BY`模式启用时，MySQL拒绝那些不遵循这一规则的查询，以避免返回不确定的结果。例如，如果你有一个表`orders`，包含`customer_id`和`order_date`两个字段，试图执行如下查询： ```sql SELECT customer_id, order_date, COUNT(*) FROM orders GROUP BY customer_id; ``` 在5.7.5之前的版本中，MySQL可能不会报错，但会返回某个特定`customer_id`下的任意`order_date`。而在5.7.5及以后的版本中，由于`order_date`未在`GROUP BY`子句中，且未进行聚合，MySQL会抛出上述错误。 **解决方案：** 有几种方法可以解决这个问题： 1. **修改SQL模式：** 你可以临时或永久地禁用`ONLY_FULL_GROUP_BY`模式。临时修改可以通过在查询前使用`SET`语句来实现： ```sql 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'; ``` 或者，如果你想全局禁用此模式，可以在my.cnf配置文件中更改`sql_mode`设置，然后重启MySQL服务： ``` [mysqld] sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION ``` 2. **使用聚合函数：** 如果你确实想获取每个分组的所有列，可以使用`GROUP_CONCAT`或其他聚合函数。例如： ```sql SELECT customer_id, GROUP_CONCAT(order_date) FROM orders GROUP BY customer_id; ``` 3. **添加到`GROUP BY`子句：** 如果你只想保留每个分组的一个`order_date`，可以选择一个特定的日期，如最早的或最新的日期： ```sql SELECT customer_id, MIN(order_date) FROM orders GROUP BY customer_id; ``` 4. **使用子查询：** 另一种方法是通过子查询来获取特定的列，这样可以避免在`SELECT`列表中直接引用非分组列： ```sql SELECT o1.customer_id, o1.order_date FROM orders o1 JOIN ( SELECT customer_id, MIN(order_date) AS min_date FROM orders GROUP BY customer_id ) o2 ON o1.customer_id = o2.customer_id AND o1.order_date = o2.min_date; ``` **总结：** 理解`ONLY_FULL_GROUP_BY`模式的含义及其在MySQL 5.7及更高版本中的作用，可以帮助我们编写更准确和可靠的查询。在大多数情况下，遵循SQL标准是最佳实践，因为它能确保结果的正确性。然而，如果确实需要回退到旧的行为，可以通过调整`sql_mode`来实现。在进行这种调整时，应充分考虑其可能带来的后果，如潜在的数据不一致性和不可预测性。正确使用`GROUP BY`和聚合函数，是避免此类错误的关键。

这个错误是由于您在使用GROUP BY子句时，SELECT语句中的列不一致导致的。在GROUP BY子句中，您必须指定所有不聚合的列。在这种情况下，您需要将a.id列添加到GROUP BY子句中： SELECT a.id, other_columns FROM your_table_name AS a GROUP BY a.id, other_columns 请注意，other_columns是您查询中的其他列。确保在GROUP BY子句中包含所有这些列，以解决此错误。

阅读全文

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'kaixi_homecare.a.id'

相关推荐

[err] 1055 - expression #1 of order by clause is not in group by clause and contains nonaggregated column information_schema.profiling.seq which is not functionally dependent on columns in group by

Expression #35 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'snowbear_data_analysis.f.的解决方法

1055 - Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test1.tb_course.course_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'train_runf

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'database_name.sh_goods_comment.goods_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Expression #8 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'app4g.P.f_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Expression#1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'nengyuanguanli.consumption_water.consumption' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'homestay.h.livable_number' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dx2.a.admin_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

expression #1 of select list is not in group by clause and contains nonaggregated column

Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'java221.score.num' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by 是哪里出错

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column whic

Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop_base.og.goods_sale_type' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'docdb.a.TITLE' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

一个使用Androidstudio开发的校园通知APP

基于粒子群的ieee30节点优化、配电网有功-无功优化 软件：Matlab+Matpowre 介绍：对配电网中有功-无功协调优化调度展开研究，通过对光伏电源、储能装置、无功电源和变压器分接头等设备协调

最新推荐

一个使用Androidstudio开发的校园通知APP

基于粒子群的ieee30节点优化、配电网有功-无功优化 软件：Matlab+Matpowre 介绍：对配电网中有功-无功协调优化调度展开研究，通过对光伏电源、储能装置、无功电源和变压器分接头等设备协调

C#自定义事件 2024年12月23日

GitHub图片浏览插件：直观展示代码中的图像

管理建模和仿真的文件

【OPPO手机故障诊断专家】：工程指令快速定位与解决

求[100，900]之间相差为12的素数对（注：要求素数对的两个素数均在该范围内）的个数

Android IPTV项目：直播频道的实时流媒体实现

"互动学习：行动中的多样性与论文攻读经历"

【OPPO手机工程模式终极指南】：掌握这些秘籍，故障排查不再难！

基于粒子群的ieee30节点优化、配电网有功-无功优化软件：Matlab+Matpowre 介绍：对配电网中有功-无功协调优化调度展开研究，通过对光伏电源、储能装置、无功电源和变压器分接头等设备协调

基于粒子群的ieee30节点优化、配电网有功-无功优化软件：Matlab+Matpowre 介绍：对配电网中有功-无功协调优化调度展开研究，通过对光伏电源、储能装置、无功电源和变压器分接头等设备协调