526互联

1789. 员工的直属部门

发布时间 2023-07-04 20:41:37作者: HHHuskie

1789. 员工的直属部门

SQL架构

Table: Employee

+---------------+---------+
| Column Name   |  Type   |
+---------------+---------+
| employee_id   | int     |
| department_id | int     |
| primary_flag  | varchar |
+---------------+---------+
这张表的主键为 employee_id, department_id
employee_id 是员工的ID
department_id 是部门的ID，表示员工与该部门有关系
primary_flag 是一个枚举类型，值分别为('Y', 'N'). 如果值为'Y',表示该部门是员工的直属部门。 如果值是'N',则否

 

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

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

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

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

返回结果没有顺序要求。

 

示例：

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            |
+-------------+---------------+--------------+

Result table:
+-------------+---------------+
| employee_id | department_id |
+-------------+---------------+
| 1           | 1             |
| 2           | 1             |
| 3           | 3             |
| 4           | 3             |
+-------------+---------------+
- 员工1的直属部门是1
- 员工2的直属部门是1
- 员工3的直属部门是3
- 员工4的直属部门是3

分两种情况进行连接，上面的为只有一个部门的，下面的为有多个部门的
(select distinct employee_id,department_id
from Employee
where (employee_id) not in (
    select 
    employee_id
from
    Employee
where 
    primary_flag = 'Y'
))

union

(select distinct
    employee_id,
    department_id
from
    Employee
where 
    primary_flag = 'Y')

1,用union把只有1个部门的员工和属于主要部门的员工做联合

select employee_id,department_id from Employee
group by 1 having count(1) = 1
union
select employee_id,department_id from Employee
where primary_flag = 'Y'

2,创建一张公用表t，选出flag=Y或计数项cnt=1的员工

with t as(
    select *,count(1) over(partition by employee_id) cnt
    from Employee
)
select employee_id,department_id from t where primary_flag='Y' or cnt=1

3,根据员工id以flag进行组内排序选出rk为1的即可

with t as(
    select *,row_number() over(partition by employee_id order by primary_flag) rk
    from Employee
)
select employee_id,department_id from t where rk = 1

 4.将所有有多个部门的非直属部门排除掉

select employee_id,department_id from Employee 
where (employee_id,primary_flag) not in
 (select employee_id,'N' from Employee where primary_flag='Y')