【面试必刷】Mysql编程题：第四期

Test 1

有一个员工表 employees 简况如下:

有一个部门领导表 dept_manager 简况如下:

请你找出所有非部门领导的员工 emp_no ，以上例子输出:

解题思路

(1) 查询的要求是不在 dept_manager 表中的 emp_no，使用 NOT IN + 子查询。MYSQL官方文档中提到 IN 使用时效率不高，推荐使用 EXISTS 或 JOIN。

SELECT emp_no 
FROM employees 
WHERE emp_no NOT IN(SELECT DISTINCT emp_no
                   FROM dept_manager)
复制代码

(2) 使用 LEFT JOIN 来连接两个表，再筛选非 manager 的员工。

SELECT e.emp_no
FROM employees AS e LEFT JOIN dept_manager AS d
ON e.emp_no = d.emp_no 
WHERE dept_no IS NULL
复制代码

(3) 使用 NOT EXISTS + 子查询 的方式。

SELECT emp_no
FROM employees AS e
WHERE NOT EXISTS(SELECT *
                FROM dept_manager AS d
                WHERE e.emp_no = d.emp_no)
复制代码

Test 2

有一个员工表 dept_emp 简况如下:

第一行表示为员工编号为 10001 的部门是 d001 部门。

有一个部门经理表 dept_manager 简况如下:

第一行表示为 d001 部门的经理是编号为 10002 的员工。

获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示，以上例子如下:

解题思路

考察知识点

INNER JOIN 内连接。

根据题中的要求，使用内连接的方式连接两张表，同时要求两表的部门编号 dept_no 要相同，但员工编号 emp_no 不能相同。

SELECT e.emp_no, m.emp_no AS manager
FROM dept_emp AS e INNER JOIN dept_manager AS m
ON e.dept_no = m.dept_no
WHERE e.emp_no <> m.emp_no
复制代码

为何要这样？一步步看！ 我们先获取两表 CROSS JOIN(笛卡尔积)的结果。

我们先指定连接条件为 e.dept_no = m.dept_no， 在这些记录中寻找部门号相同的员工。得到如下结果。

然后再指定 e.emp_no <> m.emp_no ，筛选员工编号不相等的员工信息，这样就去掉了员工的经理是他本身这种情况。结果如下。

当然 WHERE 与 ON 中的条件顺序可以颠倒，并不影响结果。