mysql - WHERE 子句后无效使用 GROUP BY 运算符

Question

以下查询成功返回打印次数最多的特定用户(按 ID):

SELECT file_prints.user_id, user.major, sum(print_pages) FROM `file_prints`
INNER JOIN `user` on file_prints.user_id = user.user_id GROUP BY
file_prints.user_id 
ORDER BY sum(print_pages)

当我引入一个where子句来尝试消除根本没有打印的用户(也就是说，where sum(print_pages) == 0)时，我编写了以下查询:

SELECT file_prints.user_id, user.major, sum(print_pages) FROM `file_prints`
INNER JOIN `user` on file_prints.user_id = user.user_id WHERE
sum(print_pages) > 0 GROUP BY file_prints.user_id 
ORDER BY sum(print_pages)

并出现以下错误:

 #1111 - Invalid use of group function 

如何消除 sum(print_pages) 为 0 的结果？

Answer 1

这是因为您不能将聚合函数与 WHERE 子句一起使用。因此，使用 HAVING 子句如下:

SELECT file_prints.user_id, user.major, sum(print_pages) 
FROM `file_prints`
INNER JOIN `user` on file_prints.user_id = user.user_id
GROUP BY file_prints.user_id
HAVING sum(print_pages) > 0 
ORDER BY sum(print_pages)