[mysql] LeetCode 184.Department Highest Salary

184.Department Highest Salary

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

문제설명

---

풀이

SELECT D.name AS Department, E.name AS Employee , E.salary AS Salary
FROM Employee E
JOIN Department D
ON E.departmentId = D.id
WHERE (Salary, E.departmentId) IN (SELECT MAX(salary), departmentId
                  FROM Employee 
                  GROUP BY departmentId);

다른풀이

WITH
A AS(
    SELECT 
            departmentId
        ,   MAX(salary) AS max_sa
    FROM employee
    WHERE 1 = 1
    GROUP BY 1
)
,B AS(
    SELECT *
    FROM A
    LEFT JOIN employee USING(departmentId)
    WHERE 1 = 1
        AND salary = max_sa
        
)
SELECT t.name AS department, b.name AS employee, salary
FROM B
LEFT JOIN department T ON t.id = B.departmentId