题解 | 员工当前薪水比其manager当前薪水高的相关信息
获取员工其当前的薪水比其manager当前薪水还高的相关信息
https://www.nowcoder.com/practice/f858d74a030e48da8e0f69e21be63bef
# 条件:当前员工,则所有表的 to_date='9999-01-01' # 思路:多表连接,将员工,部门,部门领导,员工薪水和部门领导薪水放到一行数据中,取员工薪水>部门领导薪水的记录即可 with temp as ( select dep.emp_no ,dep.dept_no ,dem.emp_no as manager_no ,sal1.salary as emp_salary ,sal2.salary as manager_salary ,if (sal1.salary>sal2.salary,1,0) as is_big from dept_emp as dep left join dept_manager as dem on dep.dept_no=dem.dept_no left join salaries as sal1 on dep.emp_no=sal1.emp_no left join salaries as sal2 on dem.emp_no=sal2.emp_no where sal1.to_date='9999-01-01' and sal2.to_date='9999-01-01' and dep.to_date='9999-01-01' and dem.to_date='9999-01-01' ) select emp_no ,manager_no ,emp_salary ,manager_salary from temp where is_big=1