[MySQL] LeetCode : Department Highest Salary Solution

 

 

https://leetcode.com/problems/department-highest-salary/

Department Highest Salary - LeetCode

 

1. 코드 구현 (where절 서브쿼리) ★

SELECT D.name 'Department', E.name 'Employee', E.salary 'Salary'
FROM Department D INNER JOIN Employee E ON D.id = E.departmentId
WHERE E.salary in (
                      SELECT MAX(salary) as maxSalary
                      FROM Employee
                      GROUP BY departmentId
                   );

 

2. 코드 구현 (CTE)

WITH TMP AS(
SELECT E.name
       ,MAX(E.salary) OVER(PARTITION BY E.departmentId)AS maxSalary
       ,salary
       ,E.departmentId
FROM Employee E 
-- WHERE 절에서는 alias 사용 불가 
-- WHERE maxSalary = E.salary
)
SELECT D.name AS Department
       ,E.name AS Employee
       ,E.Salary AS Salary
FROM TMP E INNER JOIN Department D ON E.departmentId = D.id 
WHERE E.salary = E.maxSalary

 

3. 코드 구현 (from절 서브쿼리)

SELECT MS.Department
      ,MS.name AS Employee
      ,MS.salary AS Salary
FROM (SELECT E.name
             ,MAX(E.salary) OVER(PARTITION BY E.departmentId)AS maxSalary
             ,salary
             ,D.name AS Department
             ,E.departmentId
      FROM Employee E
      INNER JOIN Department D ON E.departmentId = D.id
      )AS MS 
WHERE MS.salary = MS.maxSalary

 

4. 코드 구현 (INNER JOIN) ★

SELECT d.name AS department
       ,e.name AS employee
       ,e.salary
FROM employee AS e
    INNER JOIN(
        SELECT departmentId, MAX(salary) AS highest_salary
        FROM employee
        GROUP BY departmentId
    )AS dh ON e.departmentId = dh.departmentId
        AND e.salary = dh.highest_salary
    INNER JOIN department AS d ON e.departmentID = d.id