22，SQL训练之，力扣，1789. 员工的直属部门

• 学习：知识的初次邂逅
• 复习：知识的温故知新

• 练习：知识的实践应用

一，原题力扣链接

. - 力扣（LeetCode）

二，题干

表：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'.

请编写解决方案，查出员工所属的直属部门。

返回结果 没有顺序要求 。

返回结果格式如下例子所示：

示例 1：

输入：
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            |
+-------------+---------------+--------------+
输出：
+-------------+---------------+
| employee_id | department_id |
+-------------+---------------+
| 1           | 1             |
| 2           | 1             |
| 3           | 3             |
| 4           | 3             |
+-------------+---------------+
解释：
- 员工 1 的直属部门是 1
- 员工 2 的直属部门是 1
- 员工 3 的直属部门是 3
- 员工 4 的直属部门是 3

三，建表语句

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');

select * from employee;

四，分析

题解
字段 员工id  部门id 直属部门

题目分析
 一个员工有多个部门 也可以有一个部门
当一个员工只有一个部门的时候,这个部门无论是Y还是N都是他的直属部门
当一个员工有多个部门的时候,那么这个部门的flag是Y,才是他的直属部门

思路 一

思路一，用子查询解答

第一步，查询员工表。以员工id分组。当部门次数是1的时候 那么无论他的flag是n还是y 这个部门都是他的直属部门  所以先拿到他们的id

第二步

查询员工表，拿到部门flsg是Y的人的id 

他们所属是Y 那么这个必然是他们的直属部门 所以也无关次数了

第三步，用或者来 拼接子查询  因为题目中要求 其实又两点

第一，只有一条，无论flsg是什么 都是他的直属部门

或者

如果他又多个部门，那么flag是Y的才是他的直属部门 所以

我们用or 就可以求出

--------------------------------

思路二，用窗口函数解答

开个窗户，拿到每一个员工 的部门次数

第二步，使用case when 语法 如果他们的部门是1 那么返回他们的部门id

第三步，添加一个条件，去除部门id是null的行数

拿到最终的结果

-------------------------

思路三，使用nuion拼接2个子查询 拿到结果

第一个子查询，查询 部门的flsg 是Y的 员工和部门id

第二个子查询 使用min 或者max 可以拿到部门的id 分组一次 拿到多个列

第三步 拼接2个子查询，去重一下 使用union distinct 拿到最终的结果

五，SQL解答

解法一，子查询

-- 子查询解法
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(department_id)=1
        );

解法二，开窗行数 + case when

-- 窗口函数解法
with t1 as (
    select
     employee_id,
     department_id,
     primary_flag,
 count(department_id) over(partition by employee_id) co
     from employee
) ,t2 as (
    select employee_id,
       (case when t1.co=1 then department_id
            when t1.co>1 and t1.primary_flag='Y' then department_id
           end )as d_id,
       primary_flag from t1
)
select employee_id ,d_id as department_id from t2 where t2.d_id is not null;

解法三，使用union 拼接2个子查询

--  连接解法

select  employee_id, department_id
from employee
where primary_flag = 'Y'

union distinct

select  employee_id, min(department_id) as department_id  -- 选择一个department_id，因为可能只有一个
from employee
group by  employee_id
having count(department_id) = 1 order by employee_id;

 

 

六，验证

七，知识点总结

1. union 连接两个子查询的妙用 ；
2. 如果分组求聚合，并且我们知道这个字段只有一条，我们可以使用min或者max函数，来分组一列，展示多列的效果；
3. 使用子查询 or条件判定 两边任何一条符合就剔除对应的字段
4. 窗口函数开窗 可以统计不同的次数
5. case then 判定可以根据不同的条件拿到不同的结果
6. if null 去除空值。

• 学习：知识的初次邂逅
• 复习：知识的温故知新

• 练习：知识的实践应用