Explanation
The table and earnings data is depicted in the following diagram:
The maximum earnings value is . The only employee with earnings is Kimberly, so we print the maximum earnings value () and a count of the number of employees who have earned (which is ) as two space-separated values.
(1)WHERE절 서브쿼리 이용
SELECT months salary as earnings, count() FROM Employee
WHERE monthssalary =(SELECT MAX(months salary) FROM Employee)
GROUP BY earnings
(2)Having절 서브쿼리 이용
SELECT months salary as earnings , COUNT() FROM employee
GROUP BY earnings
HAVING earnings = (SELECT MAX(months*salary) FROM employee)
Department Highest Salary
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.
MYSQL 답안 :
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
HackerRank 문제-Top Earners
출처 : https://www.hackerrank.com/challenges/earnings-of-employees/problem?h_r=internal-search
Explanation The table and earnings data is depicted in the following diagram: The maximum earnings value is . The only employee with earnings is Kimberly, so we print the maximum earnings value () and a count of the number of employees who have earned (which is ) as two space-separated values.
(1)WHERE절 서브쿼리 이용 SELECT months salary as earnings, count() FROM Employee WHERE monthssalary =(SELECT MAX(months salary) FROM Employee) GROUP BY earnings
(2)Having절 서브쿼리 이용 SELECT months salary as earnings , COUNT() FROM employee GROUP BY earnings HAVING earnings = (SELECT MAX(months*salary) FROM employee)
출처 : https://leetcode.com/problems/department-highest-salary/
LeetCode 문제
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.
MYSQL 답안 : 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