题解 | #获取所有非manager员工当前的薪水情况#
获取员工其当前的薪水比其manager当前薪水还高的相关信息
http://www.nowcoder.com/practice/f858d74a030e48da8e0f69e21be63bef
解题思路:进行三次表的连接,第一次将员工和所属部门联系在一起,第二次匹配员工的薪资,第三次匹配部门主管的薪资。依据dept_no列相等的条件,进行部门间薪水的比较。我认为题目的难点是突破常见使用group by进行分组比较的思想(因为要输出的列,不仅仅含有分组的那列) select dep.emp_no,dem.emp_no as manager_no,s1.salary as emp_salary,s2.salary as manager_salary from dept_emp as dep join dept_manager as dem on dep.dept_no = dem.dept_no join salaries as s1 on s1.emp_no = dep.emp_no left join salaries as s2 on s2.emp_no = dem.emp_no where dep.dept_no = dem.dept_no and s1.salary > s2.salary;