在where子句中使用count:组功能的无效使用 [英] Using count in where clause : invalid use of group function

问题描述

我是数据库的初学者.我正在使用mySQL进行练习.我有两个桌子:

I am beginner in database. I am practicing on mySQL. I have two tables:

STUDENTS
sno | sName | age 

ENROLL
sno | cno

我想要一个列表，其中列出了所有参加了不止一门课程的学生. 当我尝试这样做时:

I want a list of all students who have enrolled in more than one course. When i tried this:

select distinct s.* from student s, enroll e 
where ((s.sno = e.sno) and (count(e.sno)>1));

我收到此错误:

无效使用组功能"

"Invalid Use of group function"

您能告诉我为什么我不能在where子句中使用count还是这里的问题是什么? 预先感谢.

Can you please tell me why I cant use count in where clause or what exactly is the problem here? Thanks in advance.

edit:这不是一个家庭作业问题(教授已经发布了解决方案)，我正在努力解决这个问题.

edit: This is not a homework question (professor has already posted the solution), I am struggling with the concept.

推荐答案

您只能在HAVING子句或使用GROUP BY的SELECT子句中使用像COUNT()这样的聚合函数. WHERE子句对来自FROM子句的行进行操作.没有发生任何聚合，因此聚合函数没有任何意义.

You can only use aggregate functions like COUNT() in a HAVING clause, or in the SELECT clause when a GROUP BY is used. The WHERE clause operates on rows that come from the FROM clause. No aggregation has taken place, so there is no way for the aggregate functions to be meaningful.

您似乎想要做的是从ENROLL表中找到出现多次的所有学生.然后，您想获得有关这些学生的更多详细信息.可能有很多方法可以做到这一点，但我建议您使用子查询.

It looks like what you want to do is find from the ENROLL table all students that appear more than once. Then you want to get more details about those students. There are potentially many ways to do this, but I'd recommend a subquery.

SELECT s.*
  FROM student AS s
  JOIN (
    SELECT e.sno
      FROM enroll AS e
     GROUP BY e.sno
    HAVING COUNT(*) > 1
  ) AS e
    ON e.sno = s.sno
 ORDER BY s.age DESC
 LIMIT 10

在JOIN之后的子查询进行第一个计算(学生在ENROLL中有多行)，并基本上生成一个带有学生ID列表的伪表.由于我们正在执行内部联接，因此只会显示STUDENT表中子查询中具有sno的行.基本上由ON子句解决了.

The subquery there after JOIN does that first calculation (which students have multiple rows in ENROLL) and basically produces a pseudo-table with a list of student IDs. Since we're doing an inner join, only rows in the STUDENT table that have an sno in our subquery will show up. That's basically taken care of by the ON clause.

由于您在评论中说过希望对学生应用附加条件，因此我在一些示例代码中添加了可能发生的情况.由于该信息来自STUDENT表，因此可以在子查询之外完成.您并没有具体说明年龄最大的学生"的意思，所以我只是假设您想让10个年龄最大的学生参加多门课程.您应该能够根据需要进行调整.

Since you said in a comment that you want to be able to apply additional conditions on the students, I've added in some example code where that would happen. Since that information comes from the STUDENT table, it can be done outside the subquery. You didn't specifically specifically what "oldest students" mean, so I've just assumed you wanted the 10 oldest enrolled in multiple courses. You should be able to adjust based on your needs.

让我知道这是否没有任何意义，我将尝试更详细地解释.

Let me know if any of this doesn't make sense and I'll try to explain in more detail.

这篇关于在where子句中使用count:组功能的无效使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！