SQL大神带你飞 | 24春招京东SQL真题解析-各个部门实际平均薪资和男女员工实际平均薪资

各个部门实际平均薪资和男女员工实际平均薪资

https://www.nowcoder.com/practice/e8272685d07347cc88667f31f7989231

最近做了京东的24年春招题，题目如下： alt

题目分析

在这道题目中，我们需要从两个表中提取信息并进行计算。表结构如下：

staff_tb（员工表）
- staff_id：员工编号
- staff_name：员工姓名
- staff_gender：员工性别
- department：部门
salary_tb（工资表）
- staff_id：员工编号
- normal_salary：正常工资
- dock_salary：扣除工资

目标：计算每个部门的平均实际工资、男性员工的平均实际工资、女性员工的平均实际工资。

输出要求：按平均实际工资降序排列输出。

知识点关键词：SQL连接、分组、条件聚合、排序

解答步骤

连接表格

首先，我们需要将员工表和工资表通过员工编号进行连接，以便获取每个员工的工资信息。
```
from staff_tb sf
join salary_tb sy on sf.staff_id = sy.staff_id
```
计算平均实际工资

计算每个部门的平均实际工资，实际工资为正常工资减去扣除工资。使用 round 函数保留两位小数。
```
round(avg(sy.normal_salary - sy.dock_salary), 2) as average_actual_salary
```
计算男性员工的平均实际工资

使用条件聚合 if 函数来计算男性员工的平均实际工资，若性别为男性则计算实际工资，否则为 null。使用 ifnull 函数处理可能的 null 值。
```
ifnull(round(avg(if(sf.staff_gender = 'male', sy.normal_salary - sy.dock_salary, null)), 2), 0.00) as average_actual_salary_male
```

计算女性员工的平均实际工资

类似于男性员工，使用条件聚合 if 函数来计算女性员工的平均实际工资。

ifnull(round(avg(if(sf.staff_gender = 'female', sy.normal_salary - sy.dock_salary, null)), 2), 0.00) as average_actual_salary_female

分组和排序

按部门分组，并按平均实际工资降序排列。
```
group by department
order by average_actual_salary desc
```

本题函数汇总

1. `IFNULL` 函数

IFNULL 函数用于检查一个值是否为 NULL，如果是 NULL，则返回一个指定的默认值。

语法：

IFNULL(expression, default_value)

expression：要检查的表达式。
default_value：如果 expression 为 NULL，则返回的默认值。

2. `IF` 函数

IF 函数用于根据条件返回不同的值，类似于编程语言中的 if-else 语句。

语法：

IF(condition, true_value, false_value)

condition：条件表达式。
true_value：如果条件为 TRUE，则返回的值。
false_value：如果条件为 FALSE，则返回的值。

解题代码

select sf.department,
round(avg(sy.normal_salary - sy.dock_salary), 2) as average_actual_salary,
ifnull(round(avg(if(sf.staff_gender = 'male', sy.normal_salary - sy.dock_salary, null)), 2), 0.00) as average_actual_salary_male,
ifnull(round(avg(if(sf.staff_gender = 'female', sy.normal_salary - sy.dock_salary, null)), 2), 0.00) as average_actual_salary_female
from staff_tb sf
join salary_tb sy on sf.staff_id = sy.staff_id
group by department
order by average_actual_salary desc