# Having 子句

考虑这么一个业务场景: 查询部门平均底薪超过2000元的部门编号

我们很自然想到下面这条 SQL 语句

```sql
SELECT 
    deptno 
FROM t_emp
WHERE AVG(sal)>=2000 GROUP BY deptno;
```

但事实上这句SQL语句是有逻辑错误的。我们回想SQL的执行顺序**FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT。我们需要先通过WHERE, GROUP BY 确定了统计范围以后才能使用聚合函数AVG进行统计，而上面的SQL语句在WHERE中就使用了聚合函数，相当于没有指定聚合的范围。**

正确的做法是使用HAVING

```sql
SELECT deptno FROM t_emp
GROUP BY deptno HAVING AVG(sal)>=2000 ;
```

再来一个例子

```sql
# 查询每个部门中, 入职在1982年以后的员工超过2个的 部门编号
SELECT deptno FROM t_emp
WHERE hiredate>"1982-01-01"
GROUP BY deptno HAVING COUNT(*)>=2;
```

## 总结一下

**HAVING 的作用是 解决了在分组中使用聚合函数的问题**

SQL的执行顺序**FROM -> WHERE -> GROUP BY -> SELECT -> ORDER BY -> LIMIT**


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://chesterzhang666.gitbook.io/intro-mysql/advanced-select/group-by-fen-zu-cha-xun/having-zi-ju.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.