LeetCode 1789.员工的直属部门

数据准备

drop table Employee;

Create table If Not Exists Employee (employee_id int, department_id int, primary_flag ENUM('Y','N')) ;
Truncate table Employee;
insert into Employee (employee_id, department_id, primary_flag) values ('1', '1', 'N');
insert into Employee (employee_id, department_id, primary_flag) values ('2', '1', 'Y');
insert into Employee (employee_id, department_id, primary_flag) values ('2', '2', 'N');
insert into Employee (employee_id, department_id, primary_flag) values ('3', '3', 'N');
insert into Employee (employee_id, department_id, primary_flag) values ('4', '2', 'N');
insert into Employee (employee_id, department_id, primary_flag) values ('4', '3', 'Y');
insert into Employee (employee_id, department_id, primary_flag) values ('4', '4', 'N');

需求

一个员工可以属于多个部门。

当一个员工加入超过一个部门的时候，他需要决定哪个部门是他的直属部门。

请注意，当员工只加入一个部门的时候，那这个部门将默认为他的直属部门，虽然表记录的值为’N’.

请编写一段SQL，查出员工所属的直属部门

输入

输出

1. 求出超过一个部门的员工的直属部门的信息
   

2. 求出只有一个部门的员工信息
   

3. 合并

-- 合并
with t2 as (
    select * from Employee where primary_flag='Y'
    union all
    (with t1 as (
        select employee_id,
           count(1) rn1
        from Employee
        group by employee_id
        having rn1=1
    )
    select e.*
    from t1 , Employee e
    where t1.employee_id=e.employee_id)
)
select employee_id,department_id
from t2
order by employee_id;