184.部门最高工资
题解:
SELECT T1.name department, T.name employee, T.salary
FROM employee T, department T1
WHERE T.departmentid = T1.id
AND (salary, departmentId) IN
(SELECT MAX(salary), departmentId FROM employee GROUP BY departmentId)
分析:
GROUP BY 字段名 # 根据字段进行分组
先对 DepartmentId 字段分组查询最大值,得到不同 DepartmentId 下的最大值
SELECT DepartmentId, max( Salary )
FROM Employee
GROUP BY DepartmentId
再根据 DepartmentId 字段连接 Department 表,根据 Salary 和 DepartmentId 查找 Department.Name 字段
SELECT T1.name department, T.name employee, T.salary
FROM employee T, department T1
WHERE T.departmentid = T1.id
AND (salary, departmentId) IN
(SELECT MAX(salary), departmentId FROM employee GROUP BY departmentId)