在SQL查询中,SELECT和HAVING子句的执行顺序是一个常见的困惑点。以下是SQL查询的逻辑执行顺序:
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 50000
ORDER BY avg_salary DESC;
执行顺序: 1. 从employees表获取数据 2. 应用WHERE条件过滤 3. 按department分组 4. 计算每组的平均工资 5. 使用HAVING过滤平均工资>50000的组 6. 选择department和avg_salary列 7. 按avg_salary降序排序
错误写法(在HAVING中使用SELECT别名):
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING avg_salary > 50000 -- 错误!HAVING执行时avg_salary还不存在
正确写法:
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000 -- 使用聚合函数而不是别名
理解这个执行顺序对于编写正确的SQL查询非常重要,特别是在处理分组和聚合函数时。