문제
Table: Employee
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
| department | varchar |
| managerId | int |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table indicates the name of an employee, their department, and the id of their manager.
If managerId is null, then the employee does not have a manager.
No employee will be the manager of themself.
Write a solution to find managers with at least five direct reports.
Return the result table in any order.
https://leetcode.com/problems/managers-with-at-least-5-direct-reports/description/
예시
Input:
Employee table:
+-----+-------+------------+-----------+
| id | name | department | managerId |
+-----+-------+------------+-----------+
| 101 | John | A | null |
| 102 | Dan | A | 101 |
| 103 | James | A | 101 |
| 104 | Amy | A | 101 |
| 105 | Anne | A | 101 |
| 106 | Ron | B | 101 |
+-----+-------+------------+-----------+
Output:
+------+
| name |
+------+
| John |
+------+
문제 풀이
-- JOIN 활용
SELECT
e2.name
FROM
employee AS e1
JOIN
employee AS e2 ON e1.managerid = e2.id
GROUP BY
e1.managerid
HAVING
COUNT(e1.managerid) >= 5;
-- SUBQUERY 활용
SELECT
e2.name
FROM
employee AS e2
WHERE
e2.id IN (
SELECT
e1.managerid
FROM
employee AS e1
GROUP BY
e1.managerid
HAVING
COUNT(e1.managerid) >= 5
);
SQL을 독학하시는 분들에게 도움이 되길 바라며,
혹 더 좋은 방법이 있거나 오류가 있다면 편하게 말씀 부탁드립니다.
문제
Table: Employee +-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | | department | varchar | | managerId | int | +-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table indicates the name of an employee, their department, and the id of their manager.
If managerId is null, then the employee does not have a manager.
No employee will be the manager of themself.
Write a solution to find managers with at least five direct reports.
Return the result table in any order.
https://leetcode.com/problems/managers-with-at-least-5-direct-reports/description/
예시
Input: Employee table: +-----+-------+------------+-----------+ | id | name | department | managerId | +-----+-------+------------+-----------+ | 101 | John | A | null | | 102 | Dan | A | 101 | | 103 | James | A | 101 | | 104 | Amy | A | 101 | | 105 | Anne | A | 101 | | 106 | Ron | B | 101 | +-----+-------+------------+-----------+ Output: +------+ | name | +------+ | John | +------+
문제 풀이
-- JOIN 활용 SELECT e2.name FROM employee AS e1 JOIN employee AS e2 ON e1.managerid = e2.id GROUP BY e1.managerid HAVING COUNT(e1.managerid) >= 5;
-- SUBQUERY 활용 SELECT e2.name FROM employee AS e2 WHERE e2.id IN ( SELECT e1.managerid FROM employee AS e1 GROUP BY e1.managerid HAVING COUNT(e1.managerid) >= 5 );
SQL을 독학하시는 분들에게 도움이 되길 바라며,
혹 더 좋은 방법이 있거나 오류가 있다면 편하게 말씀 부탁드립니다.