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

with
    a as(
        SELECT
	        dept_emp.*,
	        salary as emp_salary   ##计算员工及其薪资情况
        FROM dept_emp
        LEFT JOIN salaries
        USING(emp_no)
    ),    
    b as(
        SELECT
	        dept_manager.*,
	        salary as manager_salary   ##计算经理薪资情况
        FROM dept_manager
        LEFT JOIN salaries
        USING(emp_no)
    )
select 
    a.emp_no,
    b.emp_no as manager_no,    ##连接两个薪资表，比较员工与经理薪资情况
    a.emp_salary,
    b.manager_salary
from a
left join b
on a.dept_no=b.dept_no
where a.emp_salary > b.manager_salary