2019年11月18日
目录
题目 部门工资最高的员工
解题1
- 使用中间表,根据departmentId来group by分组
- 两张表department和employee联表查询后,使用中间表作为where过滤条件
SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM employee
join department
on department.id = employee.DepartmentId
WHERE (employee.DepartmentId,employee.Salary) in (SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId);
解题2
WHERE内连接 效率与官方题解相近
SELECT
b.Name AS Department,
a.Name AS Employee,
c.Max_Salary AS Salary
FROM
Employee AS a,
Department AS b,
(
SELECT DISTINCT DepartmentId, MAX(Salary) AS Max_Salary
FROM Employee
GROUP BY DepartmentId
) AS c
WHERE a.DepartmentId = c.DepartmentId
AND a.DepartmentId = b.Id
AND a.Salary = c.Max_Salary
;
解题3
-- JOIN内连接 效率最低
SELECT
b.`Name` as Department,
a.`Name` as Employee,
c.Max_Salary as Salary
FROM employee as a
join (SELECT DISTINCT DepartmentId,MAX(Salary) AS Max_Salary FROM employee GROUP BY DepartmentId) as c
on a.DepartmentId = c.DepartmentId and a.Salary = c.Max_Salary
JOIN department as b
on a.DepartmentId = b.Id
;