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

#非领导员工档期那薪资信息
WITH t0 AS
 (
SELECT
	de.emp_no,
	s.salary  AS emp_salary,
	de.dept_no
FROM  dept_emp de JOIN salaries s
ON de.emp_no=s.emp_no
WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager)
),
#领导员工的当前工资
t1 AS(
SELECT
	dm.emp_no AS manager_no,
	s.salary AS manager_salary,
	dm.dept_no
FROM dept_manager dm JOIN salaries s 
ON dm.emp_no =s.emp_no
WHERE dm.to_date='9999-01-01'
AND s.to_date='9999-01-01'
)
SELECT 
	t0.emp_no,
	t1.manager_no,
	t0.emp_salary,
	t1.manager_salary
FROM t0 JOIN t1 
ON t0.dept_no=t1.dept_no
WHERE t0.emp_salary>t1.manager_salary