题解 | #获取员工其当前的薪水比其manager当前薪水还高的相关信息#
获取员工其当前的薪水比其manager当前薪水还高的相关信息
http://www.nowcoder.com/practice/f858d74a030e48da8e0f69e21be63bef
将三个表拆分为两个表，然后过滤
源码：
SELECT a.emp_no,b.emp_no,a.salary,b.salary
FROM (
SELECT a.emp_no AS 'emp_no',a.dept_no AS 'dept_no',c.salary AS 'salary'
FROM dept_emp a
JOIN dept_manager b
ON a.emp_no <> b.emp_no AND a.dept_no = b.dept_no
JOIN salaries c
ON c.emp_no = a.emp_no
) a,(
SELECT a.emp_no AS 'emp_no',a.dept_no AS 'dept_no',c.salary AS 'salary'
FROM dept_emp a
JOIN dept_manager b
ON a.emp_no = b.emp_no AND a.dept_no = b.dept_no
JOIN salaries c
ON c.emp_no = a.emp_no
) b
WHERE a.salary > b.salary AND a.dept_no = b.dept_no
