184. Department Highest Salary(SQL)

# Medium

SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
	)
;

SELECT d.Name AS Department, e1.Name AS Employee, e1.Salary 
FROM Employee e1
    JOIN Department d ON e1.DepartmentId = d.Id 
WHERE Salary IN 
    (SELECT MAX(Salary) 
    FROM Employee e2 
    WHERE e1.DepartmentId = e2.DepartmentId);

SELECT d.Name AS Department, e.Name AS Employee, e.Salary 
FROM Employee e, Department d
WHERE e.DepartmentId = d.Id 
    AND e.Salary = 
        (SELECT MAX(Salary) 
        FROM Employee e2 
        WHERE e2.DepartmentId = d.Id);

join方法解释：先把两个表格内交成我们所需的样式，用where筛选出来同一个department里面MAX(Salary)

Previous183. Customers Who Never Order(SQL)Next185. Department Top Three Salaries(SQL)

Last updated 5 years ago

Was this helpful?