题解 | #获取员工其当前的薪水比其manager当前薪水还高的相关信息#

本题主要思路如下：

1. 获取各个部门中，最高的薪水

# 获取部门最高的工资
select 
	a.dept_no,
	max(salary)
from 
	dept_emp a
	left join
	salaries b
	on a.emp_no = b.emp_no
where b.to_date = '9999-01-01'
group by a.dept_no;

2. 获取各个部门中，部门经理的薪水

# 获取部门经理工资
select 
	b.emp_no as manager_no,
	b.salary as manager_salary
from 
	dept_manager a
	inner join 
	salaries b
on a.emp_no = b.emp_no

3. 获取各个部门中，最多薪水的员工信息，将1中的SQL聚合到本SQL中

# 获取部门最高工资员工
select 
	a.dept_no as dept_no, 
	a.emp_no as emp_no,
	b.salary as salary
from 
	dept_emp a
	inner join
	salaries b
	on a.emp_no = b.emp_no
	inner join #获取部门最高的工资，1中的SQL
    (
        select 
        a.dept_no as dept_no,
        max(salary) as max_salary
        from 
        dept_emp a
        inner join
        salaries b
        on a.emp_no = b.emp_no
        where b.to_date = '9999-01-01'
        group by a.dept_no
    ) c
    on a.dept_no = c.dept_no
where b.to_date = '9999-01-01' and c.max_salary = b.salary;

4. 聚合上述结果，取出各部门薪水高于其部门经理薪水的数据，将2，3聚合到本SQL中

select 
c.emp_no as emp_no,
b.emp_no as manager_no,
c.salary as emp_salary,
b.salary as manager_salary
from 
dept_manager a
inner join 
salaries b
on a.emp_no = b.emp_no
inner join 
(
    select 
    a.dept_no as dept_no, 
    a.emp_no as emp_no,
    b.salary as salary
    from 
    dept_emp a
    inner join
    salaries b
    on a.emp_no = b.emp_no
    inner join
    (
    select 
        a.dept_no as dept_no,
        max(salary) as max_salary
        from 
        dept_emp a
        inner join
        salaries b
        on a.emp_no = b.emp_no
        where b.to_date = '9999-01-01'
        group by a.dept_no
    ) c
    on a.dept_no = c.dept_no
    where b.to_date = '9999-01-01' and c.max_salary = b.salary
) c
on a.dept_no = c.dept_no
where b.to_date = '9999-01-01' and b.salary < c.salary