我有两个表:
Employee(EmployeeNum, Ename, Job, MGR, HireDate, SAL,COMM, DeptNo)
Department (DeptNo, DName, LOC)
我正试图找到以下内容:
Q。每个部门最近聘用的员工是谁?
查询是:
SELECT Employee.Ename,
(SELECT MAX(HireDate)
FROM Department
WHERE Employee.DeptNo = Department.DeptNo)
AS HireDate
FROM Employee;
但我得到了以下错误:
消息8120,级别16,状态1,第112行"Employee.Ename"列在选择列表中无效,因为它既不包含在聚合函数中,也不包含在GROUP BY子句中。
什么是正确的查询,可以帮助我从每个部门获得最近聘用的员工?
谢谢!
如果这是SQL Server(假设来自该错误消息(,您可以使用ROW_NUMBER()
窗口函数非常容易地实现这一点:
SELECT D.DName, E.EName, E.HireDate
FROM
Department D
INNER JOIN (
SELECT EmployeeNum, ENAme, HireDate, ROW_NUMBER() OVER (PARTITION BY DeptNo ORDER BY HireDate Desc) as hirerank
FROM Employee
) E ON D.DeptNo = E.DeptNo
WHERE E.hirerank = 1
使用最大条件时必须设置group by
:
SELECT Ename,
DeptNo
, MAX(HireDate)
FROM Employee
group by Employee.Ename,DeptNo