LeetCode : 184. Department Highest Salary

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

Department Highest Salary - LeetCode

 

FROM절 서브쿼리, JOIN

 

SELECT D.NAME AS DEPARTMENT,
        E.NAME AS EMPLOYEE,
        E.SALARY AS SALARY
FROM EMPLOYEE E
JOIN (SELECT DEPARTMENTID, MAX(SALARY) AS MX_SAL
FROM EMPLOYEE
GROUP BY DEPARTMENTID) SUB ON E.SALARY = SUB.MX_SAL 
                            AND E.DEPARTMENTID = SUB.DEPARTMENTID
JOIN DEPARTMENT D ON E.DEPARTMENTID = D.ID

 

Window Func

SELECT EMP_INFO.DEPT_NAME AS Department,
       EMP_INFO.EMP_NAME AS Employee,
       EMP_INFO.SALARY AS Salary
FROM (
    SELECT E.NAME AS EMP_NAME, 
           D.NAME AS DEPT_NAME, 
           E.SALARY,
           MAX(SALARY) OVER(PARTITION BY DEPARTMENTID) AS MAX_SAL
    FROM EMPLOYEE E
    JOIN DEPARTMENT D ON E.DEPARTMENTID = D.ID) EMP_INFO
WHERE EMP_INFO.SALARY = EMP_INFO.MAX_SAL