SQL문제풀기

[SQL 문제 풀이] Department Highest Salary (최고 급여 부서)

Jun 03, 2025

[SQL 문제 풀이] Department Highest Salary (최고 급여 부서)

Contents

내가 작성한 쿼리

Department Highest Salary - LeetCode

Can you solve this real interview question? Department Highest Salary - Table: Employee +--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+ id is the primary key (column with unique values) for this table. departmentId is a foreign key (reference columns) of the ID from the Department table. Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID of their department. Table: Department +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+ id is the primary key (column with unique values) for this table. It is guaranteed that department name is not NULL. Each row of this table indicates the ID of a department and its name. Write a solution to find employees who have the highest salary in each of the departments. Return the result table in any order. The result format is in the following example. Example 1: Input: Employee table: +----+-------+--------+--------------+ | id | name | salary | departmentId | +----+-------+--------+--------------+ | 1 | Joe | 70000 | 1 | | 2 | Jim | 90000 | 1 | | 3 | Henry | 80000 | 2 | | 4 | Sam | 60000 | 2 | | 5 | Max | 90000 | 1 | +----+-------+--------+--------------+ Department table: +----+-------+ | id | name | +----+-------+ | 1 | IT | | 2 | Sales | +----+-------+ Output: +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Jim | 90000 | | Sales | Henry | 80000 | | IT | Max | 90000 | +------------+----------+--------+ Explanation: Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.

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

Department Highest Salary - LeetCode

notion image


각 부서에서 가장 높은 급여를 받는 직원을 찾기 위한 솔루션을 작성하세요.

결과 테이블을 순서에 상관없이 반환하세요.

내가 작성한 쿼리

MySQL, Oracle


SELECT D.name AS Department,
       E.name AS Employee,
       E.salary
FROM Employee E
JOIN Department D
    ON E.departmentId = D.id
WHERE (E.departmentId, E.salary) IN (
    --  부서(departmentId)별 최고 salary 구하는 서브쿼리
    SELECT departmentId, MAX(salary)
    FROM Employee
    GROUP BY departmentId
);

Share article

More articles

[JAVA 문제 풀이] 299. 문자열 나누기

[JAVA 문제 풀이] 299. 문자열 나누기

[JAVA 문제 풀이] 298. 둘만의 암호

[JAVA 문제 풀이] 298. 둘만의 암호

[SQL 문제 풀이] Consecutive Numbers (연속된 숫자)

[SQL 문제 풀이] Consecutive Numbers (연속된 숫자)

[JAVA 문제 풀이] 297. 로또의 최고 순위와 최저 순위

[JAVA 문제 풀이] 297. 로또의 최고 순위와 최저 순위

stupefyee

RSS·Powered by Inblog