我目前正在学习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_SCOREWhat 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...
更多推荐
发布评论