与 HAVING 子句一起使用 ORDER BY 时出错
我正在尝试使用一些基本的 SQL 函数。我需要获取一些数据的平均值并按降序排列。我得到的错误是“不允许使用组函数”
表：
STUDENTS
-----------
ID
CLASS
GRADE
ROOM
SQL：
SELECT ID, class, AVG(Grade) AS AvgGrade
FROM Students
GROUP BY AVG(Grade)
HAVING AVG(Grade) >= 3.0
ORDER BY AVG(Grade) DESC
我被告知 ORDER BY 不能与 HAVING 子句一起使用，我需要重复该函数。有什么帮助吗？
I am trying to use some basic SQL functions. I need to get an average of some data and order it in descending order. The error I get is "group function is not allowed"
Table:
STUDENTS
-----------
ID
CLASS
GRADE
ROOM
SQL:
SELECT ID, class, AVG(Grade) AS AvgGrade
FROM Students
GROUP BY AVG(Grade)
HAVING AVG(Grade) >= 3.0
ORDER BY AVG(Grade) DESC
I was told that ORDER BY cannot be used with the HAVING clause and I would need to repeat the function. Any help?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
GROUP BY avg(Grade)没有任何意义。GROUP BY表达式定义您希望应用聚合的组。大概您需要
GROUP BY ID, classGROUP BY avg(Grade)doesn't make any sense.The
GROUP BYexpression defines the groups that you want the aggregate applied to.Presumably you need
GROUP BY ID, classGROUP BY下不能有avg(Grade)。在您的示例中，您必须具有：
GROUP BY ID, class。You cannot have
avg(Grade)underGROUP BY.In your example, you'd have to have:
GROUP BY ID, class.在标准 SQL 中，
ORDER BY子句中仅允许使用SELECT子句中的AS子句（“列别名”），即并非所有 SQL 产品当然，忠实地执行标准，但上述内容应该适用于 SQL Server 等。
In Standard SQL, only
ASclauses ("column aliases") from theSELECTclause are allowed in theORDER BYclause i.e.Not all SQL products faithfully implement Standards, of course, but the above should work in SQL Server, for example.