# Write your MySQL query statement below
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号雇员的薪水信息丢失了。
原站题解
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;