纳入错题本,JOIN的深刻理解。
1. 题目
-
题目来源:SQL25 获取员工其当前的薪水比其manager当前薪水还高的相关信息
-
题目描述
获取员工其当前的薪水比其manager当前薪水还高的相关信息,
第一列给出员工的emp_no,
第二列给出其manager的manager_no,
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary -
表格
-
所需结果
2. 题解
如果没有理清思路,这题就会有点难度。
一开始怎么也没想到用 (A-B)-(B-C)
的连方式(-
指JOIN),即没有一张表是现成的,都需要自己先构筑好,最后再使用条件进行连接。
(最后再去题目所需要的结果,换个顺序不就是:员工-员工工资-主管-主管
工资吗?最后的连接条件是部门相同)
# right
SELECT t1.emp_no, t2.manager_no, t1.emp_salary, t2.manager_salary
FROM (SELECT de.emp_no emp_no, s1.salary emp_salary, de.dept_no
FROM dept_emp de
JOIN salaries s1
ON de.emp_no = s1.emp_no
WHERE de.to_date = '9999=01-01'
AND s1.to_date = '9999=01-01'
AND de.emp_no NOT IN (SELECT emp_no FROM dept_manager)
) t1
JOIN (SELECT dm.emp_no manager_no, s2.salary manager_salary, dm.dept_no
FROM dept_manager dm
JOIN salaries s2
ON dm.emp_no = s2.emp_no
WHERE s2.to_date = '9999=01-01'
AND dm.to_date = '9999-01-01'
) t2
ON t1.dept_no = t2.dept_no
WHERE t2.manager_salary < t1.emp_salary
反思:JOIN:一表多用构造多个新字段 + 圆括号的使用
做了这题才发现对JOIN的理解还不够深,一开始脑海里一直很乱,甚至还想到`GROUP BY dept。
回过头看,其实就是比较不在同一张表中的内容。
一件奇葩的事情是,如下的代码报错:
# wrong
SELECT t.emp_no, t.manager_no, t.emp_salary, t.manager_salary
FROM ((SELECT de.emp_no emp_no, s1.salary emp_salary, de.dept_no
FROM dept_emp de
JOIN salaries s1
ON de.emp_no = s1.emp_no
WHERE de.to_date = '9999=01-01'
AND s1.to_date = '9999=01-01'
AND de.emp_no NOT IN (SELECT emp_no FROM dept_manager)
) t1
JOIN (SELECT dm.emp_no manager_no, s2.salary manager_salary, dm.dept_no
FROM dept_manager dm
JOIN salaries s2
ON dm.emp_no = s2.emp_no
WHERE s2.to_date = '9999=01-01'
AND dm.to_date = '9999-01-01'
) t2
ON t1.dept_no = t2.dept_no
) t
WHERE t.manager_salary < t.emp_salary
SQL中圆括号真的不能随便使用啊?