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

标签: MySQL
发布时间: 2017/1/9 22:57:15
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

下面的查询成功返回特定用户 (按 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 子句,试图消除还没打印出来的用户 (即是说,凡 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 

我如何消除具有 0 对于 sum(print_pages) 的结果?

解决方法 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)
官方微信
官方QQ群
31647020