mysql - 我正在尝试更正 T-SQL 中的此错误代码，该代码表明我有一个无效列

Question

这是我最初的问题:

“What would be the correct syntax and join (if any) of a subquery that would return all of the employees first and last name from the employee’s table, and return their department name from the department table, but only those employees who more than the average salary for their department? Thanks for your answers”

使用 Mitch Wheat 的示例(并接受他当之无愧的劝告)后，我向数据库中的 DEPARTMENT 表添加了一列，并实现了以下代码:

ALTER TABLE DEPARTMENTS  
ADD Salary money
GO
select First_Name, Last_Name, department_Name  
from Employees e join  
   (select Department_Name,AVG(Salary) AS averageSalary  
     from DEPARTMENTS d  
     join Employees e ON e.Department_Id=d.Department_Id  
     group by  Department_Name) ds 
on ds.averageSalary=e.Employee_Id 
where e.salary>ds.AverageSalary 

但是，我仍然收到此错误:

Msg 207, Level 16, State 1, Line 8
Invalid column name 'salary'.

为什么 SALARY 仍然是无效的列名？

Answer 1

您可能启用了某些区分大小写的排序规则，这会影响列名称的大小写敏感性。请注意，您添加的列是“薪水”，但您的查询引用了“薪水”。

纠正这个情况，你应该没问题。

参见this related question了解更多相关信息。

(另请注意 LittleBobbyTables(伟大的昵称，顺便说一句!)对您的查询的评论 - 那里有一个奇怪的连接子句!)