校招算法笔面试 | SQL笔试面试编程题-各个部门实际平均薪资和男女员工实际平均薪资

题目## 题目

题目链接

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

题目分析

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

1. staff_tb（员工表）

   • staff_id：员工编号
   • staff_name：员工姓名
   • staff_gender：员工性别
   • department：部门

2. salary_tb（工资表）

   • staff_id：员工编号
   • normal_salary：正常工资
   • dock_salary：扣除工资

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

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

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

解答步骤

1. 连接表格

   首先，我们需要将员工表和工资表通过员工编号进行连接，以便获取每个员工的工资信息。

   from staff_tb sf
   join salary_tb sy on sf.staff_id = sy.staff_id
   

2. 计算平均实际工资

   计算每个部门的平均实际工资，实际工资为正常工资减去扣除工资。使用 round 函数保留两位小数。

   round(avg(sy.normal_salary - sy.dock_salary), 2) as average_actual_salary
   

3. 计算男性员工的平均实际工资

   使用条件聚合 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
   

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

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

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

5. 分组和排序

   按部门分组，并按平均实际工资降序排列。

   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

近似题目练习推荐

统计出当前各个title类型对应的员工当前薪水对应的平均工资

• 知识点：分组、聚合函数、连接查询

平均工资

• 知识点：聚合函数、条件过滤

题目链接

这道题目要求我们对用户通过的题目数量进行排名。我们要做的事情如下：

1. 确定总体问题

我们需要根据每个用户通过的题目数量进行降序排名，并且对于通过题目数量相同的用户，给予相同的排名。最后，结果需要按照排名升序排列，如果排名相同，则按用户ID升序排列。

2. 分析关键问题

• 计算排名：使用DENSE_RANK窗口函数根据通过的题目数量进行降序排名。
• 排序输出：按排名升序排列，如果排名相同则按用户ID升序排列。

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

步骤1：计算排名

我们使用DENSE_RANK窗口函数根据通过的题目数量进行降序排名：

select
    id,
    number,
    dense_rank() over (
        order by
            number desc
    ) as t_rank
from
    passing_number

• DENSE_RANK() OVER (ORDER BY number DESC) AS t_rank：使用DENSE_RANK函数对通过的题目数量进行降序排名。DENSE_RANK会为相同数量的题目分配相同的排名。

步骤2：排序输出

我们使用ORDER BY按排名升序排列，如果排名相同则按用户ID升序排列：

order by
    t_rank asc,
    id asc

• ORDER BY t_rank ASC, id ASC：按排名升序排列，如果排名相同则按用户ID升序排列。

完整代码

select
    id,
    number,
    dense_rank() over (
        order by
            number desc
    ) as t_rank
from
    passing_number
order by
    t_rank asc,
    id asc;