获取当前薪水排名第二的员工

方法1

思路

通过将表 employees 和表 salaries 内连接查询就可得到输出样例中的结果，重点在于如何在不通过 order by 排序的情况下得到薪水第二多的员工记录

步骤 1

先找出 salaries 表中薪水最多的记录

select max(salary) max_salary from salaries;

步骤 2

在 where 条件中过滤掉步骤 1 中得到的薪水最多的记录，并重复 1 次步骤 1，这样就能得到薪水第二多的记录

select max(salary) secondary_salary
  from salaries
 where salary != (select max(salary) max_salary from salaries);

步骤 3

将表 employees 和表 salaries 进行联合查询，并且将薪水第二多的薪水作为过滤条件

select b.emp_no,
       b.salary,
       a.last_name,
       a.first_name
  from employees a inner join
       salaries  b on a.emp_no = b.emp_no
 where b.salary in (select max(salary) max_ssecondary_salaryalary_2
                      from salaries
                     where salary != (select max(salary) max_salary from salaries)
                    )

方法2

思路

将表 salaries 与表 salaries 进行笛卡尔连接，并以薪水作为过滤条件来得到每个员工从高到低的薪水排名

步骤 1

将表 salaries 与表 salaries 进行笛卡尔连接，并以薪水作为过滤条件，按员工来聚合查询得到每个员工从高到低的薪水排名，以及

select b.emp_no,
       b.salary,
       c.emp_no,
       c.salary 
  from salaries b cross join 
       salaries c on b.salary <= c.max_salar
 order by b.emp_no, c.emp_no;

+--------+--------+--------+--------+
| emp_no | salary | emp_no | salary |
+--------+--------+--------+--------+
|  10001 |  88958 |  10001 |  88958 |
|  10002 |  72527 |  10001 |  88958 |
|  10002 |  72527 |  10002 |  72527 |
|  10002 |  72527 |  10004 |  74057 |
|  10003 |  43311 |  10001 |  88958 |
|  10003 |  43311 |  10002 |  72527 |
|  10003 |  43311 |  10003 |  43311 |
|  10003 |  43311 |  10004 |  74057 |
|  10004 |  74057 |  10001 |  88958 |
|  10004 |  74057 |  10004 |  74057 |
+--------+--------+--------+--------+

对员工编号进行分组查询得到大于等于其薪水的员工数，也是薪水排名

select b.emp_no,
       max(b.salary) salary,
       count(*) rank_salary_desc
  from salaries b cross join 
       salaries c on b.salary <= c.salary
 group by b.emp_no;

+--------+--------+------------------+
| emp_no | salary | rank_salary_desc |
+--------+--------+------------------+
|  10001 |  88958 |                1 |
|  10002 |  72527 |                3 |
|  10003 |  43311 |                4 |
|  10004 |  74057 |                2 |
+--------+--------+------------------+

步骤 2

得到薪水第二多的员工编号及薪水

select b.emp_no,
       max(b.salary) salary,
       count(*) rank_salary_desc
  from salaries b cross join 
       salaries c on b.salary <= c.salary
 group by b.emp_no
having rank_salary_desc = 2;

+--------+--------+------------------+
| emp_no | salary | rank_salary_desc |
+--------+--------+------------------+
|  10004 |  74057 |                2 |
+--------+--------+------------------+

步骤 3

将步骤 2 中得到的薪水第二多的记录与员工信息表 employees 进行内连接以获取员工姓名信息

  select d.emp_no,
         d.salary,
         a.last_name,
         a.first_name
   from employees a inner join
        (select b.emp_no,
               max(b.salary) salary,
               count(*) rank_salary_desc
          from salaries b cross join 
               salaries c on b.salary <= c.salary
         group by b.emp_no
        having rank_salary_desc = 2
        ) d on a.emp_no = d.emp_no;

+--------+--------+-----------+------------+
| emp_no | salary | last_name | first_name |
+--------+--------+-----------+------------+
|  10004 |  74057 | Koblick   | Chirstian  |
+--------+--------+-----------+------------+