mysql命令行 - HAVING子句

我在mysql命令行这个系列里介绍过有条件查询，它用到WHERE子句。但是有时候用WHERE子句无法给我们的查询加入条件，比如在上一节我们学习的GROUP BY(按组归类)子句，如果我们要在按组归类后加入条件，用WHERE就行不通，因为WHERE只可以在按组归类前加入条件。好在mysql提供HAVING子句，它可以帮我们解决这个问题。

如何使用HAVING子句？

首先，让我们看一个GROUP BY(按组归类)的子句应用：

select title, AVG(salary)
from employee_data
GROUP BY title;
+----------------------------+-------------+
| title                      | AVG(salary) |
+----------------------------+-------------+
| CEO                        | 200000.0000 |
| Customer Service Manager   |  70000.0000 |
| Finance Manager            | 120000.0000 |
| Marketing Executive        |  77333.3333 |
| Multimedia Programmer      |  83333.3333 |
| Programmer                 |  75000.0000 |
| Senior Marketing Executive | 120000.0000 |
| Senior Programmer          | 115000.0000 |
| Senior Web Designer        | 110000.0000 |
| System Administrator       |  95000.0000 |
| Web Designer               |  87500.0000 |
+----------------------------+-------------+
11 rows in set (0.00 sec)

上述例子把员工职务按组分类并且把各个职务的平均工资列出。如果我们只想列出平均工资大于100000的职务，就要用到HAVING子句，如下：

select title, AVG(salary)
from employee_data
GROUP BY title 
HAVING AVG(salary) > 100000;
+----------------------------+-------------+
| title                      | AVG(salary) |
+----------------------------+-------------+
| CEO                        | 200000.0000 |
| Finance Manager            | 120000.0000 |
| Senior Marketing Executive | 120000.0000 |
| Senior Programmer          | 115000.0000 |
| Senior Web Designer        | 110000.0000 |
+----------------------------+-------------+
5 rows in set (0.00 sec)

注意在这种情况下，用WHERE子句是行不通的！