Notice
Recent Posts
Recent Comments
Link
일 | 월 | 화 | 수 | 목 | 금 | 토 |
---|---|---|---|---|---|---|
1 | 2 | 3 | 4 | 5 | 6 | |
7 | 8 | 9 | 10 | 11 | 12 | 13 |
14 | 15 | 16 | 17 | 18 | 19 | 20 |
21 | 22 | 23 | 24 | 25 | 26 | 27 |
28 | 29 | 30 | 31 |
Tags
- merge
- CSS
- sql
- github
- RLIKE
- L2정규화
- 온라인협업
- hackerrank
- branch
- conflict
- leetcode
- L1정규화
- elastic net
- 코딩공부
- 버전충돌
- programmers
- PYTHON
- Git
- HTML
- window function
- coding
- early stopping
- 교차 엔트로피
- mysql
- 클라우드컴퓨팅
- 선형 모형
- 편향-분산 교환
- AWS
- full request
- 깃헙협업
Archives
- Today
- Total
Im between cherry
Leet Code | MySQL | 184. Department Highest Salary 본문
184. Department Highest Salary
https://leetcode.com/problems/department-highest-salary/
Department Highest Salary - LeetCode
Level up your coding skills and quickly land a job. This is the best place to expand your knowledge and get prepared for your next interview.
leetcode.com
The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.
Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter).
# Write your MySQL query statement below
SELECT d.name AS department
, e.name AS employee
, e.salary
FROM employee AS e
INNER JOIN (
-- 부서에서 가장 많이 벌 때에 그 임금과 부서id
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
/* MAX(), RANK(), DENSE_RANK() */
SELECT ms.deparment
, ms.name AS Employee
, ms.salary
FROM(
SELECT employee.name
, employee.salary
, department.name
, MAX(salary) OVER (PARTITION BY departmentId) max_salary
FROM employee
INNER JOIN department ON employee.departmentid = department.Id
) ms
WHERE ms.salary = ms.max_salary
'데이터분석 > practice_query' 카테고리의 다른 글
Leet Code | MySQL | 197. Rising Temperature (0) | 2020.08.30 |
---|---|
Leet Code | MySQL | 185. Department Top Three Salaries (0) | 2020.08.30 |
Leet Code | MySQL | 180. Consecutive Numbers (0) | 2020.08.30 |
Leet Code | MySQL | 196. Delete Duplicate Emails (0) | 2020.08.30 |
Leet Code | MySQL | 627. Swap Salary (0) | 2020.08.30 |
Comments