SQL错误：'database.table.field不在GROUP BY中 [英] SQL error: 'database.table.field isn't in GROUP BY

问题描述

我在安装第三方软件时遇到了这个问题。我已阅读相关主题，并且他们都建议将 sql_mode 设置为一个空字符串（ SET sql_mode =''），但该解决方案并不适合我。仍然得到相同的错误。我需要在我的配置文件中设置它吗？

以下是一些有用的信息;

服务器版本：5.6.15-56-log Percona Server（GPL），Release rel63.0，Revision 519

 的MySQL> SHOW VARIABLES like'sql_mode'; 
 + --------------- + ------- + 
 |变量名|值| 
 + --------------- + ------- + 
 | sql_mode | | 
 + --------------- + ------- + 
 1行（0.00秒）
  
 mySQL查询错误：SELECT max（e.entry_id）as max，MAX（e.entry_date）as maxdate，b.blog_id，b.blog_name，b.blog_seo_name FROM blog_entries e LEFT JOIN blog_blogs b ON（b.blog_id = e.blog_id）WHERE e.entry_status ='published'AND e.entry_banish = 0 AND e.entry_featured = 0 AND b.blog_view_level ='public'AND b.blog_disabled = 0 GROUP BY e.blog_id，b.blog_name，b。 blog_seo_name ORDER BY maxdate DESC LIMIT 0,50 
 
 SQL错误：'db_name_ipb.b.blog_id'不在GROUP BY 
中SQL错误代码：1055 
  
 
 
 
任何想法？
 $ b $ h2更新
 
 
不幸的是，代码是加密的，所以没有办法尝试你的建议。另外，如上所述，更改SQL模式并不能解决我的问题。我将继续调查并更新发生问题。
 b 
 $ b 
  SELECT MAX（e.entry_id）as max，MAX（e.entry_date）as maxdate，b.blog_id，b.blog_name，b.blog_seo_name 
 FROM blog_entries e LEFT JOIN 
 blog_blogs b 
 ON b.blog_id = e.blog_id 
 WHERE e.entry_status ='published'AND e.entry_banish = 0 AND 
 e .entry_featured = 0 AND b.blog_view_level ='public'AND 
 b.blog_disabled = 0 
 GROUP BY e.blog_id，b.blog_name，b.blog_seo_name 
 ORDER BY maxdate DESC 
 LIMIT 0,50; 
  
您有 e.blog_id  code> group by 但是 b.blog_id 在选择中。您应该选择一个或另一个。
 
 
 
注意：左连接可以是内部连接。    上的条件将内部连接变为无论如何。
 
 
注意：MySQL通常不强制执行此操作。您的环境必须设置 ONLY_FULL_GROUP_BY 模式，所以你的SQL模式不包含任何东西是相当可疑的。有关设置SQL模式的更多信息，请参见此处。
 
I am getting this when I am installing a 3rd party software. I've read the related topics and all of them suggested to set sql_mode to an empty string (SET sql_mode = '') but that solution didn't work for me. Still getting the same error. Do I need to set this in my config file or something?

Here's some useful info;

Server version: 5.6.15-56-log Percona Server (GPL), Release rel63.0, Revision 519
mysql> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)
And the failing query with error message is;
mySQL query error: SELECT MAX(e.entry_id) as max, MAX(e.entry_date) as maxdate,b.blog_id, b.blog_name, b.blog_seo_name FROM blog_entries e  LEFT JOIN blog_blogs b ON ( b.blog_id=e.blog_id )   WHERE e.entry_status='published' AND e.entry_banish=0 AND e.entry_featured=0 AND b.blog_view_level='public' AND b.blog_disabled=0 GROUP BY e.blog_id, b.blog_name, b.blog_seo_name ORDER BY maxdate DESC LIMIT 0,50

SQL error: 'db_name_ipb.b.blog_id' isn't in GROUP BY
SQL error code: 1055
Any idea ?

UPDATE

Unfortunately the codes are encrypted, so there is no way to try your suggestions. Also -as specified above- changing the SQL mode didn't solve my problem. I'll continue to investigate and update the question in case a development.
解决方案
This is your query:
SELECT MAX(e.entry_id) as max, MAX(e.entry_date) as maxdate, b.blog_id, b.blog_name, b.blog_seo_name
FROM blog_entries e LEFT JOIN
     blog_blogs b
     ON b.blog_id = e.blog_id
WHERE e.entry_status = 'published' AND e.entry_banish = 0 AND
      e.entry_featured = 0 AND b.blog_view_level='public' AND
      b.blog_disabled=0
GROUP BY e.blog_id, b.blog_name, b.blog_seo_name
ORDER BY maxdate DESC
LIMIT 0,50;
You have e.blog_id in the group by but b.blog_id in the select.  You should choose one or the other.

As a note:  the left join can just be an inner join. The conditions in the where on the b table are turning it into an inner join anyway.

Note:  MySQL usually does not enforce this.  Your environment must have the ONLY_FULL_GROUP_BY mode set, so it is quite suspicious that your SQL mode does not contain anything.  More information on setting SQL modes is here.

                        
这篇关于SQL错误：'database.table.field不在GROUP BY中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！