# Write your MySQL query statement below
1789. 员工的直属部门
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
原站题解
pythondata 解法, 执行用时: 260 ms, 内存消耗: 59.3 MB, 提交时间: 2023-09-17 10:26:45
import pandas as pd
def find_primary_department(employee: pd.DataFrame) -> pd.DataFrame:
condition = (employee['primary_flag']=='Y') | (~employee['employee_id'].duplicated(keep=False))
return employee[condition].drop(columns={'primary_flag'})
def find_primary_department2(employee: pd.DataFrame) -> pd.DataFrame:
a = pd.DataFrame()
for index,df in employee.groupby("employee_id"):
if df.shape[0] > 1:
df = df[df["primary_flag"]=="Y"]
a = pd.concat([a,df])
return a[["employee_id","department_id"]]
mysql 解法, 执行用时: 306 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:28:21
# Write your MySQL query statement below
select employee_id, department_id from
(
select
*,
count(*) over(partition by employee_id) as cnt
from Employee
) a
where primary_flag='Y' or cnt=1;
mysql 解法, 执行用时: 332 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:27:34
# Write your MySQL query statement below select employee_id, if(count(employee_id)=1,department_id, sum(if(primary_flag='Y',department_id,0))) as department_id from Employee group by employee_id;
mysql 解法, 执行用时: 353 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:26:50
# Write your MySQL query statement below
SELECT
employee_id
,IF(
COUNT(department_id)=1
,department_id
,MAX(
IF(primary_flag="Y",department_id,NULL)
)
) AS department_id
FROM
Employee
GROUP BY
employee_id;
mysql 解法, 执行用时: 309 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:26:08
# Write your MySQL query statement below
SELECT employee_id AS 'employee_id',
department_id AS 'department_id'
FROM Employee
WHERE primary_flag = 'Y'
UNION #联合查询，自动去重
SELECT employee_id AS 'employee_id',
department_id AS 'department_id'
FROM Employee
GROUP BY employee_id
HAVING COUNT(department_id) = 1;