列表

详情


1965. 丢失信息的雇员

表: Employees

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
+-------------+---------+
employee_id 是这个表的主键。
每一行表示雇员的id 和他的姓名。

表: Salaries

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| salary      | int     |
+-------------+---------+
employee_id is 这个表的主键。
每一行表示雇员的id 和他的薪水。

 

写出一个查询语句,找到所有 丢失信息 的雇员id。当满足下面一个条件时,就被认为是雇员的信息丢失:

返回这些雇员的id  employee_id , 从小到大排序 

查询结果格式如下面的例子所示。

 

示例 1:

输入:
Employees table:
+-------------+----------+
| employee_id | name     |
+-------------+----------+
| 2           | Crew     |
| 4           | Haven    |
| 5           | Kristian |
+-------------+----------+
Salaries table:
+-------------+--------+
| employee_id | salary |
+-------------+--------+
| 5           | 76071  |
| 1           | 22517  |
| 4           | 63539  |
+-------------+--------+
输出:
+-------------+
| employee_id |
+-------------+
| 1           |
| 2           |
+-------------+
解释:
雇员1,2,4,5 都工作在这个公司。
1号雇员的姓名丢失了。
2号雇员的薪水信息丢失了。

原站题解

去查看

上次编辑到这里,代码来自缓存 点击恢复默认模板
# Write your MySQL query statement below

pythondata 解法, 执行用时: 373 ms, 内存消耗: 66.4 MB, 提交时间: 2024-05-27 13:00:59

import pandas as pd

def find_employees(employees: pd.DataFrame, salaries: pd.DataFrame) -> pd.DataFrame:
    # 合并
    df = pd.merge(employees, salaries, left_on='employee_id', right_on='employee_id',how='outer')
    # 筛选出丢失姓名和薪水信息的员工
    miss_info =  df[df['name'].isnull() | df['salary'].isnull()].sort_values('employee_id')

    return miss_info[['employee_id']]


def find_employees2(employees: pd.DataFrame, salaries: pd.DataFrame) -> pd.DataFrame:
    df = employees.merge(salaries, how="outer")
    return df[df.isna().any(axis=1)][["employee_id"]].sort_values("employee_id")

mysql 解法, 执行用时: 566 ms, 内存消耗: 0 B, 提交时间: 2022-06-01 10:12:06

# Write your MySQL query statement below

select 
    employee_id 
from (
    select employee_id from employees
    union all 
    select employee_id from salaries
) as t
group by 
    employee_id
having 
    count(employee_id) = 1
order by 
    employee_id;

mysql 解法, 执行用时: 590 ms, 内存消耗: 0 B, 提交时间: 2022-05-26 17:09:45

# Write your MySQL query statement below

select 
    employee_id 
from (
    select employee_id from employees
    union all 
    select employee_id from salaries
) as t
group by 
    employee_id
having 
    count(employee_id) = 1
order by 
    employee_id;

上一题