校招算法笔面试 | SQL笔试面试编程题-统计各个部门平均薪资

题目

题目链接

这道题目要求我们统计公司各个部门的平均实发薪资。我们要做的事情如下：

1. 确定总体问题

我们需要计算各个部门的平均实发薪资，要求剔除实发薪资小于4000和大于30000的员工，并按平均实发薪资降序排序。

2. 分析关键问题

• 连接表：将staff_tb和salary_tb表连接起来，以便获取每个员工的薪资信息。
• 计算实发薪资：计算每个员工的实发薪资。
• 过滤数据：剔除实发薪资小于4000和大于30000的员工。
• 计算平均实发薪资：对每个部门的员工实发薪资求平均值。
• 排序输出：按平均实发薪资降序排序。

3. 解决每个关键问题的代码及讲解

步骤1：连接表

我们使用JOIN将staff_tb和salary_tb表连接起来：

FROM 
    staff_tb s
JOIN 
    salary_tb sa ON s.staff_id = sa.staff_id

通过员工ID进行连接，以便获取每个员工的薪资信息。

步骤2：计算实发薪资

我们在SELECT语句中计算每个员工的实发薪资：

sa.normal_salary - sa.dock_salary

• sa.normal_salary - sa.dock_salary：实发薪资=标准薪资-扣除薪资，计算实发薪资。

步骤3：过滤数据

我们使用WHERE子句剔除实发薪资小于4000和大于30000的员工：

WHERE 
    (sa.normal_salary - sa.dock_salary) BETWEEN 4000 AND 30000

• BETWEEN 4000 AND 30000：只选择实发薪资在4000到30000之间的员工。

步骤4：计算平均实发薪资

我们使用AVG函数对每个部门的员工实发薪资求平均值，并使用ROUND函数保留三位小数：

ROUND(AVG(sa.normal_salary - sa.dock_salary), 3) AS avg_salary

• AVG(...)：计算每个部门的平均实发薪资。
• ROUND(..., 3)：将结果四舍五入保留三位小数。

步骤5：排序输出

我们使用ORDER BY按平均实发薪资降序排序输出结果：

ORDER BY 
    avg_salary DESC

完整代码

SELECT 
    s.department,
    ROUND(AVG(sa.normal_salary - sa.dock_salary), 3) AS avg_salary
FROM 
    staff_tb s
JOIN 
    salary_tb sa ON s.staff_id = sa.staff_id
WHERE 
    (sa.normal_salary - sa.dock_salary) BETWEEN 4000 AND 30000
GROUP BY 
    s.department
ORDER BY 
    avg_salary DESC;