MySQL HAVING子句返回空集?

本文介绍了MySQL HAVING子句返回空集?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我下面有一张桌子，我需要编写代码以提取预算大于平均预算的行.

I am having a table below, and I need to write code that extract the rows with budget greater than the average budget.

+------+-----------------+--------+ | Code | Name | Budget | +------+-----------------+--------+ | 14 | IT | 65000 | | 37 | Accounting | 15000 | | 59 | Human Resources | 240000 | | 77 | Research | 55000 | +------+-----------------+--------+

我知道这可行:

SELECT * FROM Departments WHERE Budget > (SELECT AVG(Budget) FROM Departments);

但这看起来很难看. 这篇文章似乎建议having子句可以将查询简化为:

but this looks ugly. This post seems to suggest having clause can simplify the query into:

SELECT * FROM Departments HAVING Budget > AVG(Budget);

，但它返回空集.有什么想法吗?

but it returns empty set. Any ideas?

谢谢

推荐答案

这是因为AVG()是聚合函数，应在GROUP BY或其他聚合函数中使用. 如果不是，SELECT将返回单行.例如:

This is because AVG() is aggregation function which should be used GROUP BY or with other Aggregation functions. If not, SELECT would returns single row. for example:

mysql> SELECT * FROM test; +------+--------+ | code | budget | +------+--------+ | 14 | 65000 | | 37 | 15000 | | 59 | 240000 | | 77 | 55000 | +------+--------+ 4 rows in set (0.00 sec) mysql> SELECT code, budget, AVG(budget) FROM test; +------+--------+-------------+ | code | budget | AVG(budget) | +------+--------+-------------+ | 14 | 65000 | 93750.0000 | we got one row. +------+--------+-------------+ 1 row in set (0.00 sec)

在这种情况下，HAVING budget > AVG(budget)表示65000 > 93750，它为false，因此返回空列表.

In this case, HAVING budget > AVG(budget) means 65000 > 93750 which is false, so that returns empty list.

您的第一个附件看上去并不像丑陋"；)

Your first attampt does not look like 'ugly' ;)