Oracle：不是按表达式错误分组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17746590/
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
Oracle: NOT A GROUP BY EXPRESSION ERROR
提问by max
Below is my query. I am not sure what cause the Not A group by expression error. Any help is much appreciated. =)
以下是我的查询。我不确定是什么导致 Not A group by expression 错误。任何帮助深表感谢。=)
SELECT c.courseID, c.courseName, AVG(a.Mark) as Average_Mark
FROM course c, assessment a
WHERE c.courseID = a.courseID
Group by c.courseID, c.courseName
ORDER BY Mark DESC;
回答by xlecoustillier
Try:
尝试：
SELECT c.courseid,
c.coursename,
AVG(a.mark) AS Average_Mark
FROM COURSE c
INNER JOIN ASSESSMENT a
ON c.courseid = a.courseid
GROUP BY c.courseid,
c.coursename
ORDER BY 3 DESC; -- or ORDER BY Average_Mark DESC
As you aggregate several values of Markin order to compute the average, it becomes impossible to sort on each value of Mark. You have to sort on the result of the computation, i.e. Average_Mark.
当您聚合多个 的值Mark以计算平均值时，就不可能对 的每个值进行排序Mark。您必须对计算结果进行排序，即Average_Mark。
From a more general point of view, you are allowed to ORDER BYa non SELECTed column only if this column is part of the queried tables and if you don't use any GROUP BYor DISTINCT(unless you GROUP BYthis non displayed column, then you can ORDER BYit).
从更一般的角度来看，只有当此列是查询表的一部分并且您不使用任何或（除非您使用此未显示的列，否则您可以使用它）时，您才被允许ORDER BY使用非SELECTed 列。GROUP BYDISTINCTGROUP BYORDER BY
The reason is simple: If you use GROUP BYor DISTINCT, several rows will be potentially displayed as one. Non displayed values in those "merged" rows can potentially be different from each other, making any ORDER BYimpossible on those values.
原因很简单：如果您使用GROUP BY或DISTINCT，多行可能会显示为一行。这些“合并”行中未显示的值可能彼此不同，从而使ORDER BY这些值不可能实现。
Some DBMS (MySQL at least) behave differently, allowing ORDERing BYnon displayed values, even with GROUP BY. But MySQL seems then to order by the first encountered value of non displayed value (see fiddle). So, better keep in mind that this should be avoided, to prevent unpredictible results.
某些DBMS（MySQL的至少）的行为不同，允许ORDER荷兰国际集团BY非显示值，即使有GROUP BY。但是 MySQL 然后似乎按非显示值的第一个遇到的值排序（请参阅fiddle）。因此，最好记住应该避免这种情况，以防止出现不可预测的结果。
EDIT:See the documentationabout MySQL GROUP BYhandling.
编辑：请参阅有关 MySQL处理的文档GROUP BY。
回答by schurik
try
尝试
ORDER BY Average_Mark
instead of
代替
ORDER BY Mark
