SQL学习笔记——Unknown column xxx in having clause

代码1：

select DepartmentId 
from Employee
group by DepartmentId
having Salary=max(Salary);

报错：

Unknown column 'XXXX' in 'having clause

原因：

表根据Department将表的数进行分组，在执行Salary=max(Salary)这一句时，max(Salary)选出每一组最大的Salary，但是等号左边的Salary，并不知道指定的是哪一行的数据，因此会报错。

代码2：

select d.Name as Department,Employee.Name as Employee,Employee.Salary
from Department as d,Employee
where d.Id=Employee.DepartmentId 
      and 
      d.Id in 
           (select Employee.DepartmentId 
            from Employee
            group by DepartmentId
            having Salary = max(Salary));

在执行上面的子查询时，同样是 Salary = max(Salary)，却可以执行成功。是因为虽然没有指定，但是这里的Salary是指的外层查询传进来的数据。根据SQL语句的执行顺序，上面的代码，先执行from语句，分别从Department表和Employee表中选出一条数据，传递进入子查询中。having子句中，虽然没有指定Salary来自哪里，这里应该默认是指传进来的数据。代码改成下面的样子应该更好理解一点。

代码3：

select d.Name as Department,e.Name as Employee,e.Salary
from Department as d,Employee as e
     where d.Id=e.DepartmentId 
           and d.Id in (select Employee.DepartmentId 
                        from Employee
                        group by DepartmentId
                        having e.Salary = max(Salary));

两者的代码执行结果是相同的，但代码3更能直观的看出，每条数据来自哪里。