https://leetcode.com/problems/department-top-three-salaries/description/
Department Top Three Salaries - LeetCode
Can you solve this real interview question? Department Top Three Salaries - Table: Employee +--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------
leetcode.com
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
#Write your MySQL query statement below
with salary_rank as(
select id, name, dense_rank() OVER (PARTITION BY departmentId ORDER BY salary DESC) AS 'rank'
from Employee
)
select
d.name as 'Department'
, e.name as 'Employee'
, e.salary
from Employee e
left join Department d
on e.departmentId = d.id
left join salary_rank s
on s.id = e.id
where s.rank<=3
|
cs |
rank 함수를 쓰시면 안돼구 dense_rank를 쓰셔야 되고
서브쿼리로 만든 후에 join걸으셔서 사용하시면 됩니다.
반응형
'SQL' 카테고리의 다른 글
| leetcode 1731. The Number of Employees Which Report to Each Employee (0) | 2023.06.16 |
|---|---|
| leetcode 1211. Queries Quality and Percentage (0) | 2023.06.13 |
| leetcode 619. Biggest Single Number (0) | 2023.06.09 |
| leetcode 1517. Find Users With Valid E-Mails (0) | 2023.06.07 |
| leetcode 1633. Percentage of Users Attended a Contest (0) | 2023.06.01 |
댓글