코딩테스트 SQL 코드카타 (Primary Department)

문제

Primary Department for Each Employee - LeetCode

 

Employees can belong to multiple departments. When the employee joins other departments, 
they need to decide which department is their primary department.
Note that when an employee belongs to only one department, their primary column is 'N'.

Write a solution to report all the employees with their primary department. 
For employees who belong to one department, report their only department.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Employee table:
+-------------+---------------+--------------+
| employee_id | department_id | primary_flag |
+-------------+---------------+--------------+
| 1           | 1             | N            |
| 2           | 1             | Y            |
| 2           | 2             | N            |
| 3           | 3             | N            |
| 4           | 2             | N            |
| 4           | 3             | Y            |
| 4           | 4             | N            |
+-------------+---------------+--------------+
Output: 
+-------------+---------------+
| employee_id | department_id |
+-------------+---------------+
| 1           | 1             |
| 2           | 1             |
| 3           | 3             |
| 4           | 3             |
+-------------+---------------+
Explanation: 
- The Primary department for employee 1 is 1.
- The Primary department for employee 2 is 1.
- The Primary department for employee 3 is 3.
- The Primary department for employee 4 is 3.

---

풀이

1. employee_id, department_id 표시
2. primary_flag Y 필터
3. employee_id 그룹화 하고 count 1필터
4. employee_id 에 해당하는 department_id 출력

 

---

코드

employee_id를 바로 그룹화 할 순 없어서 

where에 조건으로 하나더 생성함.

select employee_id, department_id
from Employee
where primary_flag = "Y" 
or employee_id in 
(select employee_id
 from Employee
 group by employee_id
 having count(employee_id) = 1)

---

링크

LeetCode/1789-primary-department-for-each-employee at main · K-MarkLee/LeetCode

LeetCode/1789-primary-department-for-each-employee at main · K-MarkLee/LeetCode