题解 | 员工当前薪水比其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

全部评论

相关推荐

程序员猪皮:看不到八股什么意思
点赞 评论 收藏
分享
不愿透露姓名的神秘牛友
10-15 14:22
点赞 评论 收藏
分享
评论
点赞
收藏
分享
牛客网
牛客企业服务