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';
code> group by 但是
+ --------------- + ------- +
|变量名|值|
+ --------------- + ------- +
| sql_mode | |
+ --------------- + ------- +
1行(0.00秒)
$ c $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
$ c
任何想法?
$ b $ h2更新
不幸的是,代码是加密的,所以没有办法尝试你的建议。另外,如上所述,更改SQL模式并不能解决我的问题。我将继续调查并更新发生问题。
b
$ bSELECT 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 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 thegroup by
butb.blog_id
in theselect
. You should choose one or the other.As a note: the
left join
can just be aninner join
. The conditions in thewhere
on theb
table are turning it into aninner 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屋!