集合函数和排序的SQL问题(SQL issues with aggregate functions and ordering)

集合函数和排序的SQL问题(SQL issues with aggregate functions and ordering)

我目前正在学习SQL，到目前为止我遇到了一些问题。 特别是这一个：我有一个大学数据库（我们将使用一个EXAM表，但在这种情况下真的不重要）。

我试图按照他们在总决赛中的平均水平排列这些学生。 它应该是这样的：

SELECT STUDENT_ID, AVG(SCORE) AS AVG_SCORE FROM EXAM GROUP BY AVG_SCORE;

终端在大多数情况下都抱怨“没有一个组功能”，在这种情况下“无效标识符”。 根据我的理解，我不能一起使用两个聚合函数，比如

max(avg(score))

这是为什么 ？ 如何解决问题？

I'm currently in the process of learning SQL, and I'm encountering a few problems so far. In particular this one : I have a University database (we will be using an EXAM table, but that really doesn't matter in this case).

I am trying to order the students by their average at finals. It should look like this :

SELECT STUDENT_ID, AVG(SCORE) AS AVG_SCORE FROM EXAM GROUP BY AVG_SCORE;

The terminal complains with a "not a single group function" most of the time, and in this case "invalid identifier". From what I understood, I cannot use two aggregate functions together, like

max(avg(score))

Why is that ? how to counter the problem ?

最满意答案

你没有说明你正在使用的RDBMS。 所以以下可能不完全符合您的需求...

您不想按AVG_SCORE ...

SELECT STUDENT_ID, AVG(SCORE) AS AVG_SCORE FROM EXAM GROUP BY STUDENT_ID ORDER BY AVG_SCORE

你真正想要的是按STUDENT_ID进行分组，以获得每个学生的平均STUDENT_ID 。

ORDER BY能够使用列的别名...

You did not state the RDBMS you are using. So the following might not exactly fit to your needs...

You do not want to group by AVG_SCORE...

SELECT STUDENT_ID, AVG(SCORE) AS AVG_SCORE FROM EXAM GROUP BY STUDENT_ID ORDER BY AVG_SCORE

What you really want is to group by STUDENT_ID in order to get the average per student.

The ORDER BY is able to use a column's alias...