리트코드 문제 링크

첫번째 풀이 도전

해설 보기 전 혼자 풀어봤음. 당연히 오답!
도전결과: 각 부서별 최고 임금 한명씩 밖에 안나옴

# SELECT departmentid, MAX(salary) 
# FROM employee
# GROUP BY departmentid

# {"headers": ["departmentid", "MAX(salary)"], 
# "values": [[1, 90000], 
#            [2, 80000]]}

SELECT dp.name AS dp_name
      ,ep.name AS employee_name
      ,sub.maxsal AS max_salary
FROM department AS dp 
    INNER JOIN (
                SELECT departmentid, MAX(salary) AS maxsal
                FROM employee
                GROUP BY departmentid) AS sub 
                ON dp.id = sub.departmentid
    INNER JOIN employee AS ep ON ep.salary = sub.maxsal
GROUP BY sub.maxsal
ORDER BY sub.maxsal DESC

두번째 풀이 도전

해설 한번 보고 두번째 도전.
풀이과정 천천히 다시 적어봄.

-- 서브쿼리: departmentID 별 최고임금 확인 
SELECT departmentid, MAX(salary) AS max_salary 
FROM employee
GROUP BY departmentid

["departmentid", "max_salary"] 
[1, 90000] 
[2, 80000]

-- employee 테이블에 서브쿼리 INNER JOIN
-- INNER JOIN 기준은 id 그리고(AND) max salary. 2개 기준으로 묶어준다.
왜?? 서브쿼리에 있는 departmentid와 max_salary 두 쿼리에 모두 일치하는 항목만 뽑아주기위해!
departmentid 만 일치시키면 max_salary가 불일치해짐.
max_salary 만 일치시키면 다른 department에 동일한 salary 가진 사람이 있을 수 있음.

SELECT *
FROM employee AS e
    INNER JOIN (
    SELECT departmentid, MAX(salary) AS max_salary 
    FROM employee
    GROUP BY departmentid ) AS sub
    ON e.salary = sub.max_salary
    AND e.departmentid = sub.departmentid

{"headers": ["id", "name", "salary", "departmentId", "departmentid", "max_salary"], 
"values": [[2, "Jim", 90000, 1, 1, 90000], 
           [3, "Henry", 80000, 2, 2, 80000], 
           [5, "Max", 90000, 1, 1, 90000]]}

-- SELECT 수정으로 컬럼 정리
SELECT e.departmentid AS department
      ,e.name AS employee
      ,sub.max_salary AS Salary
FROM employee AS e
    INNER JOIN (
                SELECT departmentid, MAX(salary) AS max_salary 
                FROM employee
                GROUP BY departmentid ) AS sub
                ON e.salary = sub.max_salary

{"headers": ["department", "employee", "Salary"], 
"values": [[1, "Jim", 90000], 
           [2, "Henry", 80000], 
           [1, "Max", 90000]]}


-- department 부분을 부서명으로 바꿔주기 위해 
-- department 테이블 INNER JOIN 하고 SELECT 쿼리 수정

SELECT d.name AS department
      ,e.name AS employee
      ,sub.max_salary AS Salary
FROM employee AS e
    INNER JOIN (
                SELECT departmentid, MAX(salary) AS max_salary 
                FROM employee
                GROUP BY departmentid ) AS sub
                ON e.salary = sub.max_salary
    INNER JOIN department AS d ON d.id = sub.departmentid

{"headers": ["department", "employee", "Salary"], 
"values": [["IT", "Jim", 90000], 
           ["Sales", "Henry", 80000], 
           ["IT", "Max", 90000]]}

정답쿼리

SELECT d.name AS department
      ,e.name AS Employee
      ,e.salary AS salary
FROM Employee AS e
    INNER JOIN (
         -- departmendID 별로 top salary 데이터 구함
           SELECT departmentid, MAX(salary) AS max_salary 
            FROM employee
            GROUP BY departmentid
            ) AS dh ON e.departmentid = dh.departmentid
                   AND e.salary = dh.max_salary
    INNER JOIN department AS d ON d.id = e.departmentid

문제풀며 알게 된 것

• NNER JOIN 에 ON 조건을 2개 할 수 있구나.
  INNER JOIN ~ ON a = b AND c = d
• 한줄한줄씩 하면 조금씩 풀리는데 인내심이 한계가 온다 ㅜㅜㅜ