SQL 查询抛出“不在聚合函数或 group by 子句中”异常
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/581043/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL query throws "not in aggregate function or group by clause" exception
提问by Nik Reiman
I'm working on repairing the test suite for a project of ours, which is being tested through Hibernate/DBUnit. There are several test cases which all throw a similar exception from Hibernate, which looks something like this:
我正在为我们的一个项目修复测试套件，该项目正在通过 Hibernate/DBUnit 进行测试。有几个测试用例都从 Hibernate 抛出了一个类似的异常，看起来像这样：
java.sql.SQLException: Not in aggregate function or group by clause: org.hsqldb.Expression@109062e in statement [... blah ...]
java.sql.SQLException：不在聚合函数或 group by 子句中：org.hsqldb.Expression@109062e 在语句中 [... blah ...]
Through my googling, I am suspicious that this is caused by our use of the aggregate function AVG(), as this is in the exception's message, and all of the queries that throw contain it. However, I discovered several links to people who were getting this error, and were able to fix it by either commenting out an "ORDER BY" or "GROUP BY" clause, or by including the other columns from the SELECT clause in the grouping. I understand why this would fix such an error message, but I'm not sure whether it applies to my situation, because I tried doing the same and it made no difference. Also, we have some test cases throwing exceptions which use ORDER/GROUP, but not all. For example:
通过我的谷歌搜索，我怀疑这是由我们使用聚合函数 AVG() 引起的，因为这是在异常消息中，并且所有抛出的查询都包含它。但是，我发现了几个指向遇到此错误的人的链接，并且能够通过注释掉“ORDER BY”或“GROUP BY”子句或通过将 SELECT 子句中的其他列包含在分组中来修复它。我明白为什么这会修复这样的错误消息，但我不确定它是否适用于我的情况，因为我尝试过这样做并且没有任何区别。此外，我们有一些使用 ORDER/GROUP 抛出异常的测试用例，但不是全部。例如：
ThingerVO myThinger = (ThingerVO)session.createQuery("SELECT new ThingerVO(" +
"r.id, " + "u.id, " + "u.alias, " + "s.id, " +
"s.name, " + "r.URL," + "AVG(v.rating), " +
"r.totalCount, " + "r.isPrivate, " + "a.id, " +
"a.name, " + "r.transactionId, " + "r.size, " +
"u.hasPicture " +
") FROM Thinger r LEFT OUTER JOIN r.votes as v, Table1S s " +
"JOIN s.Table2A AS a, User u " +
"WHERE r.userId = u.id AND " +
"s.id = r.Table1SId AND " +
"r.id = :thingId")
.setInteger("thingId", thingId)
.uniqueResult();
This query also causes the same exception to be thrown, even though it doesn't use an ORDER/GROUP clause. Also, I cut/pasted the generated HSQL code from Hibernate directly into the MySQL query browser, and it ran without problems. Also, it's worth pointing out that all of this code works fine on our production database, so I'm really confused as to why it throws here.
此查询也会导致抛出相同的异常，即使它不使用 ORDER/GROUP 子句。此外，我将 Hibernate 生成的 HSQL 代码直接剪切/粘贴到 MySQL 查询浏览器中，并且它运行没有问题。此外，值得指出的是，所有这些代码在我们的生产数据库上都可以正常工作，所以我真的很困惑为什么它会抛出这里。
Some other potentially useful information -- we're using a flat XML database structure with some dummy test data for the test cases, and the MySQL dialect for hibernate. We're using dbunit 2.4.3/hibernate 3.2.6. I tried using the latest hibernate, version 3.3.1, but it behaved the same.
其他一些可能有用的信息——我们使用了一个扁平的 XML 数据库结构，其中包含一些用于测试用例的虚拟测试数据，以及用于休眠的 MySQL 方言。我们正在使用 dbunit 2.4.3/hibernate 3.2.6。我尝试使用最新的休眠版本 3.3.1，但它的行为相同。
Any pointers or hints would be greatly appreciated.
任何指针或提示将不胜感激。
回答by AJ.
If you use an aggregate function (e.g. AVG()) in the SELECT part of the SQL query along with other non-aggregate expressions, then you must have a GROUP BY clause which should list all the non-aggregate expressions.
如果您AVG()在 SQL 查询的 SELECT 部分与其他非聚合表达式一起使用聚合函数（例如），那么您必须有一个 GROUP BY 子句，它应该列出所有非聚合表达式。
I'm not familiar with java, but looking at the code, it looks like it's going to create and run a query something like this (not quite right, but close enough, I think):
我不熟悉 java，但查看代码，它看起来像是要创建和运行这样的查询（不太正确，但我认为足够接近）：
SELECT r.id,
u.id,
u.alias,
s.id,
s.name,
r.URL,
AVG(v.rating),
r.totalCount,
r.isPrivate,
a.id,
a.name,
r.transactionId,
r.size,
u.hasPicture
FROM Thinger r
LEFT OUTER JOIN r.votes as v,
Table1S s
JOIN s.Table2A AS a, User u
WHERE r.userId = u.id
AND s.id = r.Table1SId
AND r.id = :thingId
... This has no GROUP BY, but does mix aggregate and non-aggregate expressions in the SELECT clause. The problem is that the SQL is badly formed.
... 这没有GROUP BY，但在 SELECT 子句中混合了聚合和非聚合表达式。问题是 SQL 格式错误。
The fix would be to add a GROUP BYto the end of the query.
解决方法是GROUP BY在查询的末尾添加一个。
I can't say why this is working in your production system, but I suspect that there is some subtle difference there. Perhaps something is adding the GROUP BYautomatically?
我不能说为什么这在您的生产系统中有效，但我怀疑那里有一些细微的差异。也许有些东西正在GROUP BY自动添加？
Can you post a printout of the SQL it executes?
您可以发布它执行的 SQL 的打印输出吗？
回答by Aleksander Adamowski
Also, ORDER BYdoes not work in hsqldb when the order-by field is not a String.
此外，ORDER BY当 order-by 字段不是字符串时，在 hsqldb 中不起作用。
Unfortunately, this results in the Not in aggregate function or group by clauseerror message, which suggests a grouping problem, hence the confusion...
不幸的是，这会导致Not in aggregate function or group by 子句错误消息，这表明存在分组问题，因此混淆...
回答by Andrius V.
In some systems (for example TALEND) query doesn't work if there are comment lines example:
在某些系统（例如 TALEND）中，如果有注释行示例，则查询不起作用：
SELECT r.id,
u.alias,
AVG(v.rating),
r.totalCount
FROM Thinger r
LEFT OUTER JOIN r.votes as v,
Table1S s
JOIN s.Table2A AS a, User u
WHERE r.userId = u.id
AND s.id = r.Table1SId
AND r.id = :thingId
--AND r.name is not null
GROUP BY r.id, u.alias, r.totalCount
Gives an error for MS SQL queries. Instead of comment line
为 MS SQL 查询提供错误。而不是注释行
--
——
use these symbols for commenting
使用这些符号进行评论
/* AND r.name is not null */
/* AND r.name 不为空 */
Maybe it will help someone and save some time.
也许它会帮助某人并节省一些时间。
