MySQL HAVING子句返回空集? [英] MySQL HAVING Clause return empty set?

问题描述

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

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' ;)

这篇关于MySQL HAVING子句返回空集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！