# Write your MySQL query statement below
577. 员工奖金
选出所有 bonus < 1000 的员工的 name 及其 bonus。
Employee 表单
+-------+--------+-----------+--------+ | empId | name | supervisor| salary | +-------+--------+-----------+--------+ | 1 | John | 3 | 1000 | | 2 | Dan | 3 | 2000 | | 3 | Brad | null | 4000 | | 4 | Thomas | 3 | 4000 | +-------+--------+-----------+--------+ empId 是这张表单的主关键字
Bonus 表单
+-------+-------+ | empId | bonus | +-------+-------+ | 2 | 500 | | 4 | 2000 | +-------+-------+ empId 是这张表单的主关键字
输出示例：
+-------+-------+ | name | bonus | +-------+-------+ | John | null | | Dan | 500 | | Brad | null | +-------+-------+
相似题目
原站题解
pythondata 解法, 执行用时: 397 ms, 内存消耗: 66.8 MB, 提交时间: 2024-05-27 11:24:15
import pandas as pd
def find_employees_with_bonus2(employee: pd.DataFrame, bonus: pd.DataFrame) -> pd.DataFrame:
# Perform a left join on empid
merged_df = pd.merge(employee, bonus, how='left', on='empid')
# Replace NaN values in 'bonus' column with 0
merged_df['bonus'].fillna(0, inplace=True)
# Filter the DataFrame based on the condition where bonus < 1000
result_df = merged_df[merged_df['bonus'] < 1000]
# Select the 'name' and 'bonus' columns
result_df = result_df[['name', 'bonus']]
return result_df
def employee_bonus(employee: pd.DataFrame, bonus: pd.DataFrame) -> pd.DataFrame:
df = employee.merge(bonus, how='left', on='empId')[['name', 'bonus']]
return df[(df['bonus'].isna()) | (df['bonus'] < 1000)]
mysql 解法, 执行用时: 581 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:55:30
# Write your MySQL query statement below
SELECT
e.name,
b.bonus
FROM Employee e
LEFT JOIN Bonus b
ON e.empId = b.empId
WHERE IFNULL(b.bonus,0) < 1000;
mysql 解法, 执行用时: 536 ms, 内存消耗: 0 B, 提交时间: 2023-04-02 11:54:54
# Write your MySQL query statement below select name, bonus from Employee left join Bonus on Employee.EmpId = Bonus.EmpId where bonus is null or bonus < 1000