(1055, "'bbs02.app01_category.name' isn't in GROUP BY")
后台报错:[err] 1055 -- 'xxx' isn't in GROUP BY
解决方案:
初步判断是数据库(版本?配置?)的问题
进入mysql 的my.ini配置文件
ctrl+f 搜索找到字段:ONLY_FULL_GROUP_BY,将其删除即可
修改之后,需要重启mysql
找到电脑服务设置,重新启动MYSQL服务
出错原因:
SQL-92和更早版本不允许选择列表,HAVING条件或ORDER BY列表引用未在GROUP BY子句中命名的非聚合列的查询。例如,此查询在标准SQL-92中是非法的,因为name选择列表中的非聚合列不会出现在GROUP BY: <span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#0077aa">SELECT</span> o<span style="color:#999999">.</span>custid<span style="color:#999999">,</span> c<span style="color:#999999">.</span><span style="color:#0077aa">name</span><span style="color:#999999">,</span> <span style="color:#dd4a68">MAX</span><span style="color:#999999">(</span>o<span style="color:#999999">.</span>payment<span style="color:#999999">)</span> <span style="color:#0077aa">FROM</span> orders <span style="color:#0077aa">AS</span> o<span style="color:#999999">,</span> customers <span style="color:#0077aa">AS</span> c <span style="color:#0077aa">WHERE</span> o<span style="color:#999999">.</span>custid <span style="color:#a67f59">=</span> c<span style="color:#999999">.</span>custid <span style="color:#0077aa">GROUP</span> <span style="color:#0077aa">BY</span> o<span style="color:#999999">.</span>custid<span style="color:#999999">;</span></code></span></span> 要使查询在SQL-92中合法,name 必须从选择列表中省略该列,或在该GROUP BY子句中对该列进行命名 。 SQL:1999及更高版本允许每个可选功能T301的非聚合,如果它们在功能上依赖于 GROUP BY列:如果name和 之间存在这样的关系custid,则查询是合法的。例如,这是custid一个主要的关键customers。 MySQL 5.7.5及更高版本实现了对功能依赖的检测。如果ONLY_FULL_GROUP_BY启用了 SQL模式(默认情况下是这样),则MySQL拒绝查询,其中选择列表,HAVING条件或 ORDER BY列表引用非聚合列,这些列既不在GROUP BY子句中命名也不在功能上依赖于它们。(在5.7.5之前,MySQL不检测功能依赖性, ONLY_FULL_GROUP_BY默认情况下不启用。有关5.7.5之前行为的描述,请参阅MySQL 5.6参考手册。) 如果ONLY_FULL_GROUP_BY禁用,则标准SQL使用的MySQL扩展 GROUP BY允许选择列表, HAVING条件或ORDER BY列表引用非聚合列,即使列在功能上不依赖于GROUP BY列。这导致MySQL接受前面的查询。在这种情况下,服务器可以自由选择每个组中的任何值,因此除非它们相同,否则所选的值是不确定的,这可能不是您想要的。此外,添加一个ORDER BY条款不会影响每个组的值的选择。结果集排序在选择值后发生,并且 ORDER BY不会影响服务器选择的每个组中的哪个值。ONLY_FULL_GROUP_BY当您知道由于数据的某些属性时,每个未分配的每个非聚合列中的所有值GROUP BY对于每个组都是相同的,因此禁用 非常有用 。 您可以ONLY_FULL_GROUP_BY通过使用 ANY_VALUE()引用非聚合列来实现相同的效果而不禁用 。 以下讨论演示了函数依赖性,MySQL在缺少函数依赖时产生的错误消息,以及在没有函数依赖性的情况下使MySQL接受查询的方法。 ONLY_FULL_GROUP_BY启用 此查询可能无效, 因为address选项列表中的非聚合列未在GROUP BY 子句中命名: <span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#0077aa">SELECT</span> <span style="color:#0077aa">name</span><span style="color:#999999">,</span> address<span style="color:#999999">,</span> <span style="color:#dd4a68">MAX</span><span style="color:#999999">(</span>age<span style="color:#999999">)</span> <span style="color:#0077aa">FROM</span> t <span style="color:#0077aa">GROUP</span> <span style="color:#0077aa">BY</span> <span style="color:#0077aa">name</span><span style="color:#999999">;</span></code></span></span> 如果查询是name主键t或是唯一NOT NULL列,则查询有效。在这种情况下,MySQL会识别所选列在功能上依赖于分组列。例如,如果name是主键,则其值确定值,address因为每个组只有一个主键值,因此只有一行。因此,address组中值的选择没有随机性 ,也不需要拒绝查询。 如果查询name不是主键t或唯一NOT NULL列,则查询无效。在这种情况下,不能推断出功能依赖性并发生错误: <span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#0077aa">name</span><span style="color:#999999">,</span> address<span style="color:#999999">,</span> <span style="color:#dd4a68">MAX</span><span style="color:#999999">(</span>age<span style="color:#999999">)</span> <span style="color:#0077aa">FROM</span> t <span style="color:#0077aa">GROUP</span> <span style="color:#0077aa">BY</span> <span style="color:#0077aa">name</span><span style="color:#999999">;</span> <span style="color:#555555">ERROR 1055 (42000)<span style="color:#999999">:</span> Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.t.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by</span></code></span></span> 如果您知道,对于给定的数据集, 每个name值实际上唯一地确定该address值,address 在功能上是有效依赖的 name。要告诉MySQL接受查询,可以使用以下ANY_VALUE()函数: <span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#0077aa">SELECT</span> <span style="color:#0077aa">name</span><span style="color:#999999">,</span> <span style="color:#dd4a68">ANY_VALUE</span><span style="color:#999999">(</span>address<span style="color:#999999">)</span><span style="color:#999999">,</span> <span style="color:#dd4a68">MAX</span><span style="color:#999999">(</span>age<span style="color:#999999">)</span> <span style="color:#0077aa">FROM</span> t <span style="color:#0077aa">GROUP</span> <span style="color:#0077aa">BY</span> <span style="color:#0077aa">name</span><span style="color:#999999">;</span></code></span></span> 或者,禁用 ONLY_FULL_GROUP_BY。 然而,前面的例子非常简单。特别是,您不太可能在单个主键列上进行分组,因为每个组只包含一行。有关在更复杂查询中显示函数依赖性的附加示例,请参见第12.20.4节“函数依赖性检测”。 如果查询具有聚合函数且没有GROUP BY子句,则在启用的选择列表,HAVING条件或 ORDER BY列表中 不能包含非聚合列ONLY_FULL_GROUP_BY: <span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#a67f59">mysql></span> <span style="color:#0077aa">SELECT</span> <span style="color:#0077aa">name</span><span style="color:#999999">,</span> <span style="color:#dd4a68">MAX</span><span style="color:#999999">(</span>age<span style="color:#999999">)</span> <span style="color:#0077aa">FROM</span> t<span style="color:#999999">;</span> <span style="color:#555555">ERROR 1140 (42000)<span style="color:#999999">:</span> In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'mydb.t.name'; this is incompatible with sql_mode=only_full_group_by</span></code></span></span> 没有GROUP BY,只有一个组,它是不确定的,name为组选择值。ANY_VALUE()如果nameMySQL选择的值不重要,也可以使用这里 : <span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#0077aa">SELECT</span> <span style="color:#dd4a68">ANY_VALUE</span><span style="color:#999999">(</span><span style="color:#0077aa">name</span><span style="color:#999999">)</span><span style="color:#999999">,</span> <span style="color:#dd4a68">MAX</span><span style="color:#999999">(</span>age<span style="color:#999999">)</span> <span style="color:#0077aa">FROM</span> t<span style="color:#999999">;</span></code></span></span> 在MySQL 5.7.5及更高版本中,ONLY_FULL_GROUP_BY 还会影响使用DISTINCT和查询的处理 ORDER BY。考虑表的情况下,t有三列c1,c2以及 c3包含这些行: <span style="color:#555555"><span style="color:black"><code class="language-none">c1 c2 c3 1 2 A 3 4 B 1 2 C</code></span></span> 假设我们执行以下查询,期望按以下顺序排序结果c3: <span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#0077aa">SELECT</span> <span style="color:#0077aa">DISTINCT</span> c1<span style="color:#999999">,</span> c2 <span style="color:#0077aa">FROM</span> t <span style="color:#0077aa">ORDER</span> <span style="color:#0077aa">BY</span> c3<span style="color:#999999">;</span></code></span></span> 要订购结果,必须首先删除重复项。但要这样做,我们应该保留第一行还是第三行?这种任意选择会影响保留值c3,从而影响排序并使其任意。要防止出现此问题,如果任何 表达式不满足以下条件中的至少一个条件,则该查询具有 DISTINCT和ORDER BY被拒绝为无效ORDER BY: 表达式在选择列表中等于1 表达式引用并属于查询所选表的所有列都是选择列表的元素 标准SQL的另一个MySQL扩展允许HAVING子句中的引用到选择列表中的别名表达式。例如,以下查询返回 name在表中只出现一次的值orders: <span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#0077aa">SELECT</span> <span style="color:#0077aa">name</span><span style="color:#999999">,</span> <span style="color:#dd4a68">COUNT</span><span style="color:#999999">(</span><span style="color:#0077aa">name</span><span style="color:#999999">)</span> <span style="color:#0077aa">FROM</span> orders <span style="color:#0077aa">GROUP</span> <span style="color:#0077aa">BY</span> <span style="color:#0077aa">name</span> <span style="color:#0077aa">HAVING</span> <span style="color:#dd4a68">COUNT</span><span style="color:#999999">(</span><span style="color:#0077aa">name</span><span style="color:#999999">)</span> <span style="color:#a67f59">=</span> <span style="color:#990055">1</span><span style="color:#999999">;</span></code></span></span> MySQL扩展允许HAVING在聚合列的子句中使用别名 : <span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#0077aa">SELECT</span> <span style="color:#0077aa">name</span><span style="color:#999999">,</span> <span style="color:#dd4a68">COUNT</span><span style="color:#999999">(</span><span style="color:#0077aa">name</span><span style="color:#999999">)</span> <span style="color:#0077aa">AS</span> c <span style="color:#0077aa">FROM</span> orders <span style="color:#0077aa">GROUP</span> <span style="color:#0077aa">BY</span> <span style="color:#0077aa">name</span> <span style="color:#0077aa">HAVING</span> c <span style="color:#a67f59">=</span> <span style="color:#990055">1</span><span style="color:#999999">;</span></code></span></span> 注意 在MySQL 5.7.5之前,启用 ONLY_FULL_GROUP_BY禁用此扩展,因此要求HAVING 使用非混淆表达式编写子句。 标准SQL只允许GROUP BY子句中的列表达式,因此这样的语句无效,因为它FLOOR(value/100)是一个非列表达式: <span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#0077aa">SELECT</span> id<span style="color:#999999">,</span> <span style="color:#dd4a68">FLOOR</span><span style="color:#999999">(</span><span style="color:#0077aa">value</span><span style="color:#a67f59">/</span><span style="color:#990055">100</span><span style="color:#999999">)</span> <span style="color:#0077aa">FROM</span> <em>tbl_name</em> <span style="color:#0077aa">GROUP</span> <span style="color:#0077aa">BY</span> id<span style="color:#999999">,</span> <span style="color:#dd4a68">FLOOR</span><span style="color:#999999">(</span><span style="color:#0077aa">value</span><span style="color:#a67f59">/</span><span style="color:#990055">100</span><span style="color:#999999">)</span><span style="color:#999999">;</span></code></span></span> MySQL扩展标准SQL以允许GROUP BY子句中的非列表达式, 并认为前面的语句有效。 标准SQL也不允许GROUP BY子句中的别名。MySQL扩展标准SQL以允许别名,因此编写查询的另一种方法如下: <span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#0077aa">SELECT</span> id<span style="color:#999999">,</span> <span style="color:#dd4a68">FLOOR</span><span style="color:#999999">(</span><span style="color:#0077aa">value</span><span style="color:#a67f59">/</span><span style="color:#990055">100</span><span style="color:#999999">)</span> <span style="color:#0077aa">AS</span> val <span style="color:#0077aa">FROM</span> <em>tbl_name</em> <span style="color:#0077aa">GROUP</span> <span style="color:#0077aa">BY</span> id<span style="color:#999999">,</span> val<span style="color:#999999">;</span></code></span></span> 别名val在GROUP BY子句中被视为列表达式。 在GROUP BY子句中存在非列表达式的情况下,MySQL会识别该表达式与选择列表中的表达式之间的相等性。这意味着在ONLY_FULL_GROUP_BY启用SQL模式的情况下,包含的查询GROUP BY id, FLOOR(value/100)是有效的,因为FLOOR()在选择列表中会出现相同的 表达式。但是,MySQL不会尝试识别对非GROUP BY列表达式的函数依赖性,因此以下查询在ONLY_FULL_GROUP_BY启用时无效 ,即使第三个选定表达式是id列的简单公式和子句中的 FLOOR()表达式 GROUP BY: <span style="color:#555555"><span style="color:black"><code class="language-sql"><span style="color:#0077aa">SELECT</span> id<span style="color:#999999">,</span> <span style="color:#dd4a68">FLOOR</span><span style="color:#999999">(</span><span style="color:#0077aa">value</span><span style="color:#a67f59">/</span><span style="color:#990055">100</span><span style="color:#999999">)</span><span style="color:#999999">,</span> id<span style="color:#a67f59">+</span><span style="color:#dd4a68">FLOOR</span><span style="color:#999999">(</span><span style="color:#0077aa">value</span><span style="color:#a67f59">/</span><span style="color:#990055">100</span><span style="color:#999999">)</span> <span style="color:#0077aa">FROM</span> <em>tbl_name</em> <span style="color:#0077aa">GROUP</span> <span style="color:#0077aa">BY</span> id<span style="color:#999999">,</span> <span style="color:#dd4a68">FLOOR</span><span style="color:#999999">(</span><span style="color:#0077aa">value</span><span style="color:#a67f59">/</span><span style="color:#990055">100</span><span style="color:#999999">)</span><span style="color:#999999">;</span></code></span></span> 解决方法是使用派生表: SELECT id, F, id+F FROM (SELECT id, FLOOR(value/100) AS F FROM tbl_name GROUP BY id, FLOOR(value/100)) AS dt; 参考地址:https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
————————————————
版权声明:本文为CSDN博主「xiangwang2016」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/xiangwang2016/article/details/87828331
愿君前程似锦,归来仍是少年