问题描述
我下面有一张桌子，我需要编写代码以提取预算大于平均预算的行.
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子句返回空集?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！