题解 | #查找所有员工的last_name和first_name以及对应的dept_name#

解题思路

1.题目条件“包括暂时没有分配部门的员工”，故只能使用外联结

2.联结表employees、表dept_emp，获取字段emp_no、last_name、first_name、dept_no 组成新表a

SELECT s1.emp_no,s1.last_name,s1.first_name,d1.dept_no
FROM employees AS s1
LEFT JOIN dept_emp AS d1
ON s1.emp_no=d1.emp_no;

3.联结表dept_emp、表departments，获取字段dept_no、dept_name、emp_no 组成新表b

SELECT d2.emp_no,d2.dept_no,de1.dept_name
FROM dept_emp AS d2
INNER JOIN departments AS de1 
ON d2.dept_no=de1.dept_no;

4.联结表a、b 查询对应的last_name、first_name、dept_name

SELECT a.last_name,a.first_name,b.dept_name
FROM 
(SELECT s1.emp_no,s1.last_name,s1.first_name,d1.dept_no
FROM employees AS s1
LEFT JOIN dept_emp AS d1
ON s1.emp_no=d1.emp_no) AS a

LEFT JOIN

(SELECT d2.emp_no,d2.dept_no,de1.dept_name
FROM dept_emp AS d2
INNER JOIN departments AS de1 
ON d2.dept_no=de1.dept_no) AS b

ON a.dept_no=b.dept_no
AND a.emp_no=b.emp_no;