校招算法笔面试 | SQL笔试面试编程题-获取当前薪水第二多的员工的emp

题目## 题目

题目链接

这道题目要求我们从薪水表中找出薪水第二高的员工编号及其对应的薪水。我们要做的事情如下：

1. 确定总体问题

我们需要使用窗口函数对薪水进行排名，找出排名为第二的薪水记录，如果有多个员工的薪水为第二高，则需要输出所有这些员工的信息，并按员工编号升序排列。我们需要使用窗口函数来实现排名，并筛选出排名为第二的记录。

2. 分析关键问题

使用窗口函数进行排名：对所有员工的薪水进行降序排名。
筛选薪水第二高的员工：找出排名为第二的薪水记录。
排序输出：按员工编号升序排列输出结果。

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

步骤1：使用窗口函数进行排名

我们使用DENSE_RANK窗口函数对所有员工的薪水进行降序排名：

select
    emp_no,
    salary,
    dense_rank() over (
        order by
            salary desc
    ) as rk
from
    salaries

DENSE_RANK() OVER (ORDER BY salary DESC) AS rk：对所有员工的薪水进行降序排名，考虑有多个员工的薪水为第二高的情况，DENSE_RANK会为相同薪水的员工分配相同的排名。

步骤2：筛选薪水第二高的员工

我们在外层查询中筛选出排名为第二的薪水记录：

where
    rk = 2

WHERE rk = 2：筛选出薪水排名为第二的员工。

步骤3：排序输出

我们使用ORDER BY按员工编号升序排列输出结果：

order by
    emp_no asc

ORDER BY emp_no ASC：按员工编号升序排列。

完整代码

select
    emp_no,
    salary
from
    (
        select
            emp_no,
            salary,
            dense_rank() over (
                order by
                    salary desc
            ) as rk
        from
            salaries
    ) s
where
    rk = 2
order by
    emp_no asc;

题目链接

这道题目要求我们查找出那些员工的当前薪水高于其部门经理的员工和经理的信息。我们要做的事情如下：

1. 确定总体问题

这道题目要求我们查找出那些员工的当前薪水高于其部门经理的员工编号、经理编号、员工薪水和经理薪水。我们需要从部门员工表、部门经理表和薪水表中提取数据，通过部门编号和员工编号进行连接，并筛选出员工薪水高于经理薪水的记录。

2. 分析关键问题

连接表：将dept_emp、dept_manager和salaries表连接起来，以便获取每个员工和经理的薪水信息，可以一表多用，salaries表和员工id链接读出员工薪水，然后和经理id链接读出经理薪水。
筛选员工薪水高于经理的记录：通过条件过滤，找出员工薪水高于经理薪水的记录。

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

步骤1：连接表

我们使用JOIN将dept_emp、dept_manager和salaries表连接起来：

from
    dept_emp d
    join dept_manager dm on dm.dept_no = d.dept_no
    join salaries s1 on s1.emp_no = d.emp_no
    join salaries s2 on s2.emp_no = dm.emp_no

JOIN dept_manager dm ON dm.dept_no = d.dept_no：通过部门编号连接部门员工关系表和部门经理表。
JOIN salaries s1 ON s1.emp_no = d.emp_no：通过员工编号连接部门员工关系表和薪水表，获取员工的薪水。
JOIN salaries s2 ON s2.emp_no = dm.emp_no：通过经理编号连接部门经理表和薪水表，获取经理的薪水。

步骤2：筛选员工薪水高于经理的记录

我们使用WHERE子句筛选出员工薪水高于经理薪水的记录：

where
    s1.salary > s2.salary

WHERE s1.salary > s2.salary：筛选出员工薪水高于经理薪水的记录。

完整代码

select
    d.emp_no,
    dm.emp_no as manager_no,
    s1.salary as emp_salary,
    s2.salary as manager_salary
from
    dept_emp d
    join dept_manager dm on dm.dept_no = d.dept_no
    join salaries s1 on s1.emp_no = d.emp_no
    join salaries s2 on s2.emp_no = dm.emp_no
where
    s1.salary > s2.salary;